Strange PHP/MySQL error



  • I've got this: Table 'implements_pt.forummain' doesn't exist when I try to select something from forumMain table in the database implements_pt.

    Both exists, the table name is forumMain and I've changed the code to reflect that but still the same error. If you need the code:

    code tags' don't work!

    <?php
     session_start();
     require("../include/first.inc");
     //A page is 50 threads
    ?>
    <html>
    <head>
    <title>Forums</title>
    <link rel="stylesheet" type="text/css"
    href="forumLayout.css" mce_href="forumLayout.css">
    </head>
    <body>
    <body link="#36485C" vlink="#122436" alink="#545454">
    <?php
     require("./include/menu.html");
     //preperation
     $topic = $_GET['topic'];
     $dbName = 'implements_'.$_GET['db'];
     $connection = mysql_connect($host,$dbName,$pass);
     $db = mysql_select_db($dbName) or die("Could not connect to database<b><br />".mysql_error()."</b><br />");
     $sql = 'SELECT * FROM forummain WHERE topic = \''.$topic.'\' ORDER BY updateTime DESC';
     $threads = mysql_query($sql) or die(mysql_error());
     $sql = 'SELECT COUNT(*) FROM forummain WHERE topic = \''.$topic.'\'';
     $row = mysql_fetch_array(mysql_query($sql));
     $numThreads = $row[0];
     $pages = (integer)numThreads/50 + 1; //can't have x.xx pages, +1 for the x.xx
     if(isset($_GET['page']) == false)
     {
      $sql = 'SELECT * FROM forummain WHERE topic = \''.$topic.'\' ORDER BY updateTime DESC LIMIT 50';
      $threads = mysql_query($sql) or die(mysql_error());
      $pageNum = 1;
     }else  //----------NOTE INACCURATE IF AFTER FIRST PAGE IS A POST HAS BEEN DELETED! LOOK UP!!!---------
     {
      $startNum = 50 * $_GET['page'];
      $sql = 'SELECT * FROM forummain WHERE topic = \''.$topic.'\' AND idNum <= $startNum ORDER BY updateTime DESC LIMIT 50';
      $threads = mysql_query($sql) or die(mysql_error());
      $pageNum = $_GET['page'];
     } 
    ?>
    <!-- header div -->
    <div id="hdr">
      <p align="center"><img border="0" src="../NEWLOGO.jpg" mce_src="../NEWLOGO.jpg" width="412" height="91"></div>
    <!-- center column -->
    <div id="c-col">
    <div><font size="4" color="#FFFFFF"><?php echo "<a href='./newThread.php?db=".$_GET['db']."&topic=".$topic."'>New Thread</a>"; ?></font></div>
    <table width="100%" border="1" cellpadding="5" bgcolor="#888890">
     <tr>
       <td width="80%">Title<br /></td>
       <td width="*">Replies</td>
       <td width="*">Views</td>
       <td width="15%">Last Reply</td>
     </tr>
     <?php
      while($row = mysql_fetch_array($threads))
      {
       echo "<tr>";
       echo "\t<td width='80%'><a href='./view.php?db=".$_GET['db']."&id=".$row[numId]."&topic=".$topic."'>".$row[title]."</a><br />".$row[poster]."</td>";
       echo "\t<td width='*'>".$row[numReplies]."</td>";
       echo "\t<td width='*'>".$row[numViews]."</td>";
       echo "\t<td width='15%'>".$row[updateTime]."</td>";
       echo "</tr>";
      }
     ?>
    </table>
    <p align="right">
    <font color="#FFFFFF">
    <?php
     echo "Page: $pageNum of ";
     if($pages==1)
      echo '1';
     else if($pages ==2)
      echo '1, 2';
     else if($pages ==3)
      echo '1, 2, 3';
     else{
      echo '1, 2...';
      
      echo 'Last Page';
     }
    ?>
    </font>
    </p>
    </div>
    <?php
     require('../include/functions.inc');
     require('./include/lastColumn.inc');
     mysql_close();
    ?>
    </body>
    </html>

     

     Note: code is incomplete so...don't fuss about the page numbers.



  • In MySQL, table names are case sensitive when installed on a *nix computer, but case insensitive when installed on a windows computer. I note that in your code you used the table name 'forummain', but said that it exists as 'forumMain' in your introduction.



  • lesson learned:

     Never use case differences in DB-related names.



  • $topic = $_GET['topic'];

    $sql = 'SELECT COUNT(*) FROM forummain WHERE topic = \''.$topic.'\'';

     

    Is this secure? Shouldn't you use the function addslashes()? Or this the same result if you used addslashes?



  • Yes, it's insecure.

    It's quite open to QueryString/SQL injection, just like [code] $dbName = 'implements_'.$_GET['db'];[/code].

    There are more things offish or wrong about the code, but I think it'd be rude if I'd coldly toss Malfist a list -- also because I don't know his experience with PHP. I can't chastise a man because he doesn't know some detail when he starts out. :)

    My basic security when querying with ID is to use is_numeric() and die of it's not, and for simple names, you can strip all funk like quotes and semicolons, and see if it returns any records.



  • When doing this in .NET :

    Will

    SqlCommand sc = new SqlCommand("SELECT [TOPIC] FROM [FORUMTOPICS] WHERE [TOPIC_ID]=@topicid");

    sc.Parameters.Add("@topicid", SqlDbTypes.Int).Value =  myTopicId;

     

    Suffice?? 

     

    I just started learning about security lately, .NET seems to have more "built-in" stuff for it than PHP.



  • Yup, I hope you plan to refurbish your style a little. You should use templates, or more contemporarily ( to my point of view ) put the logic ( arguments processing, sql queries ) in a separate file, having only very simple and explicit php calls embedded in the HTML code. This way it's easier to reread, modify and spot security leaks. I don't want to promote over-designed applications, this is real basic and it's necessary.

    In fact this almost looks like PHPNuke / PhpBB source code :'( I don't want to be rude but that's not acceptable in redistributed/professional code.

    By the way, this code may be safe if magic_quotes is on. But that's a thing I wouldn't rely upon. 



  • @dhromed said:

    Yes, it's insecure.

    It's quite open to QueryString/SQL injection, just like [code] $dbName = 'implements_'.$_GET['db'];[/code].

    There are more things offish or wrong about the code, but I think it'd be rude if I'd coldly toss Malfist a list -- also because I don't know his experience with PHP. I can't chastise a man because he doesn't know some detail when he starts out. :)

    My basic security when querying with ID is to use is_numeric() and die of it's not, and for simple names, you can strip all funk like quotes and semicolons, and see if it returns any records.

     

    Throw me the list, please. Explain why it's a security hole and how to fix it (or generaly how one would fix it). I see the problem with the $_GET['db'] now, I'll use numbers and have a class that I can use to get the database connection. BTW this is basicly my first PHP experance and database too. I"m mainly Java, very little database work.



  • BTW, this is just volunteer work for a friend's poetry website. It's not a job or anything, I'm not quiet out of High School (37 days left (school days)).



  • A nice little explanation of SQL injection:

    The php function you'll want to use to prevent it is mysql_real_escape_string. You should be able to find plenty of documentation/examples via Google, or searching directly on the php web site.



  • I know about that function and use it too on fields where the user inputs data (I just didn't think about it for the get). I'll read that article. (BTW, that function only works with a live connection to a database, any reason why?)



  • @malfist said:

    (BTW, that function only works with a live connection to a database, any reason why?)

    I've wondered that myself. Maybe it's getting some information from the server about how it should properly escape the strings. Or maybe it's just poorly designed. :) I don't know for sure either way.



  • @Ice^^Heat said:

    $topic = $_GET['topic'];

    $sql = 'SELECT COUNT(*) FROM forummain WHERE topic = ''.$topic.''';

     

    Is this secure? Shouldn't you use the function addslashes()? Or this the same result if you used addslashes?

    addslashes()? I'd go for mysql_real_escape_string(), since it exists anyway.
    Or use interface that understands parameters...



  • The page does not direct database manipulation anymore, I have it all parsed though an object I created last night.

    [pre]

    <FONT size=2>

    <?php

    class DatabaseManager{

    var $dbName;

    var $connection;

    var $db;

    var $topic;

     

    function DatabaseManager($database = "implements", $password = "", $hoster = "localhost", $topic2 = "")

    {

    if(is_numeric($database))

    {

    switch ($databse)

    {

    case 0:

    $this->dbName = "implements_pt";

    break;

    case 1:

    $this->dbName = "implements_ct";

    break;

    case 2:

    $this->dbName = "implements_nt";

    break;

    case 3:

    $this->dbName = "implements_wt";

    break;

    default:

    $this->dbName = "ERROR";

    }

    }else

    $this->dbName = $database;

    $this->connection = mysql_connect($hoster,$this->dbName,$password);// or die("dm.1".mysql_error());

    $this->db = mysql_select_db($this->dbName);// or die("dm.2: ".mysql_error());

    $this->topic = $topic2;

    }

    /**

    * Focres safer quering

    *

    * @param $query the query without the SELECT as a part of it,.

    */

    function select($query)

    {

    $safe = mysql_real_escape_string($query); //makes query safe

    $result = mysql_query("SELECT ".$safe);

     

    return $result;

    }

    /**

    * Focres safer quering

    *

    * @param $query the query without the INSERT as a part of it,.

    */

    function insert($query)

    {

    $safe = mysql_real_escape_string($query); //makes query safe

    $result = mysql_query("INSERT ".$safe);

     

    return $result;

    }

    function count($table)

    {

    $sql = "count(*) from $table";

    $result = $this->select($sql);

    $row = mysql_fetch_array($result);

    $current = $row[0];

     

    return $current;

    }

    /**

    * Focres safer quering

    */

    function query($query)

    {

    $safe = mysql_real_escape_string($query); //makes query safe

    $result = mysql_query("SELECT ".$safe);

    $row = mysql_fetch_array($result);

    $num = $row[0];

     

    return $num;

    }

    /**

    * Closes database connection and hopefully

    */

    function closeConnection()

    {

    mysql_close();

    unset($this);

    }

    /**

    * @return Database Name

    */

    function getDatabaseName()

    {

    return $this->dbName;

    }

    /**

    * @return Database number, more secure

    */

    function getDatabaseNum()

    {

    if($this->dbName == "implements_pt")

    return 0;

    elseif($this->dbName == "implements_ct")

    return 1;

    elseif($this->dbName == "implements_nt")

    return 2;

    elseif($this->dbName == "implements_wt")

    return 3;

    else

    return -1;

    }

    function makeSafe($query)

    {

    return mysql_real_escape_string($query);

    }

    /**

    * Use only if already safe, and if makeing it safe interfears with quotes

    */

    function unsafeQuery($query)

    {

    return mysql_query($query);

    }

    }

    ?>[/pre]

    </FONT>

    Note: if I use unsafeQuery I send everything through makeSafe first, it's used because when I do topic = 'wp' or what ever, it excapes the the quotes and ruins the sql.



  • And yes, I know that @return and @param and all that does nothing in php comments because there are no phpdocs.



  • <font size="2">
    function select($query) {
    $safe = mysql_real_escape_string($query); //makes query safe
    $result = mysql_query("SELECT ".$safe);

    return $result; }
    </font>

    This is a WTF.

    What if i wanted to
    select(" * FROM user WHERE username='$username' ");



    Woops, single quoted string just got escaped.


    Personally i rather just prepare a sql statement.

    function query($query,$params=array(),$link) {
    for ($i=0;$i<count($params);$i++) {
    $params[$i] = mysql_real_escape_string($params[$i]);
    }

    $prep_query = vsprintf($query,$params);

    return mysql_query($prep_query,$link);
    }

    then just call it like

    $result = query("SELECT * FROM `user` WHERE `username`='%s' ",array($username),$link);
    typed it from memory so there might be a mistake in it, but that's the gist of it. 
     
    -edit-
     
    woops, sorry didn't notice you where the first poster, thought you where some guy trying to pimp his db class.
    so umm. interpeted the above less nasty. 


  • what does the: '%s'  do?



  • The %s has to do with his use of vsprintf inside the function.  This is his way of doing parameterized queries.

    If you're just starting your project, I highly recommend just using the PEAR::DB package, even though it generally fails as a true abstraction layer (MDB2 is much better for that).  Think of PEAR::DB as just handling all the parameterizing and low-level result processing for you.  See documentation for usage examples (especially the getAll() function).



  • @db2 said:

    @malfist said:
    (BTW, that function only works with a live connection to a database, any reason why?)

    I've wondered that myself. Maybe it's getting some information from the server about how it should properly escape the strings. Or maybe it's just poorly designed. :) I don't know for sure either way.

    According to the article, it does exactly that: It queries the database server which chars it uses for escaping. That way you're save from bad surprises if the database server uses escape chars you didn't know of.



  • @stratos said:

    function query($query,$params=array(),$link) {
    for ($i=0;$i<count($params);$i++) {
    $params[$i] = mysql_real_escape_string($params[$i]);
    }

    $prep_query = vsprintf($query,$params);

    return mysql_query($prep_query,$link);
    }
    then just call it like
    $result = query("SELECT * FROM user WHERE username='%s' ",array($username),$link);

    I'm not understanding how that would work. I see that query would = "SELECT" and link would be the database connection but how would you know the * or what is in the where clause or order by or limit or...



  • @malfist said:

    @stratos said:
    $result = query("SELECT * FROM user WHERE username='%s' ",array($username),$link);

    I'm not understanding how that would work. I see that query would = "SELECT" and link would be the database connection but how would you know the * or what is in the where clause or order by or limit or...

    Really... WTF?

    * means all columns
    WHERE, ORDER, LIMIT, etc. is whatever you write in query text.... first %s is replaced with first $params element ($username here), next %s by second .... you get the idea

    Aren't you trolling by any chance?...



  • No, there isn't always an order, limit, where, etc in the query. I'm talking about how you know where to put each of the $param elements. What if you don't want all columns, what if you want more than one but not all. How is that method supposed to work?

    I don't understand how it would work and no, I'm not trolling.



  • The order of $params == the order of the %s tokens and you pass in the SQL statement you want.  The query method just escapes the parameters and does a replacement of the tokens. It doesn't matter what the where, order by, etc is.

    example:

    $sql =  "select UserId, FirstName, LastName from Users where UserName = '%s' and Enabled = '%s' order by UserId Limit 5";

    $params[0] = "user'one"; // user names can have apostrophes, go figure

    $params[1] = "Y";  // cause the database might not have bit fields -- looking at you Oracle 9 and below

    $result = query($sql,$params,$link);

    The sql generated in query would be

          select UserId, FirstName, LastName from Users where UserName = 'user<EC>'one' and Enabled 'Y' order by UserId Limit 5 -- where <EC> is the database escape character

    IMO, its not the best way to do things, but if you data access library doesn't support parameterized queries, there's not many choices left.
     



  • Okay, I see it! $query doesn't just contain SELECT or INSERT or DELETE or whatever. I see now. I've never used the sprintf function before so...

     

    Thanks.



  • @malfist said:

    And yes, I know that @return and @param and all that does nothing in php comments because there are no phpdocs.

     Yes there are.

     Exact same syntax no less.

     And as noted before, your "safe" queries won't work like that. You have to safeify arguments, not queries.

     Edit: I see a better query() function has been presented already. Please ignore, should have read before I posted.



  • Thanks, I didn't know about phpdocs. And of course the download is blocked because SourceForge.net offers 'shareware' downloads (but download.com isn't blocked? Hummm, did money change hands for that?)


Log in to reply