i need some help here.

I have this query:

$order = isset($_GET['order']) ? mysql_real_escape_string($_GET['order']) : 'title';
$query = mysql_query("SELECT * FROM entry ORDER BY $order ASC");

You can either order by title, date or author.

But if someone gives $order something else it goes:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\entries.php on line 20

How do I get rid of this error message?

Thanks

Accepted Answer

You shouldn't treat column identifiers the same as string literals. In your example, you could end up with SQL like this:

SELECT * FROM entry ORDER BY O\'Hare ASC

Which would result in a syntax error in any SQL parser.

I have a few tips:

  1. Put your SQL into a variable, don't try to build it inside the call to mysql_query(). If you use a variable, you can now inspect the SQL string, which makes errors like the above easier to catch.

    $sql = "SELECT * FROM entry ORDER BY $order ASC";
    // here you can log $sql, or output to Firebug, etc.
    $query = mysql_query($sql);
    
  2. Check that the return value does not indicate an error. You need to check for error states, because they can occur for many reasons.

    $query = mysql_query($sql);
    if ($query === false) {
      die(mysql_error());
    }
    
  3. Use mysql_real_escape_string() for strings -- not column names, table names, SQL keywords, etc. What I use instead is an associative array that maps the $_GET input to a valid column name, so I know it's safe. This also allows you to use different values in your app parameters than the names of columns.

    $ordercolumns = array(
      "t" => "title",
      "d" => "date"
    );
    $order = "title"; // the default
    if (isset($_GET["order"]) && isset($ordercolumns[$_GET["order"]])) {
      $order = $ordercolumns[$_GET["order"]];
    }
    // now we know $order can only be 'title' or 'date', 
    // so there's no need to escape it.
    $sql = "SELECT * FROM entry ORDER BY $order ASC";
    
Written by Bill Karwin
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki