How to create a secure mysql prepared statement in php?

I am new to using prepared statements in mysql with php. I need some help creating a prepared statement to retrieve columns.

I need to get information from different columns. Currently for a test file, I use the completely unsecure SQL statement:

$qry = "SELECT * FROM mytable where userid='{$_GET['userid']}' AND category='{$_GET['category']}'ORDER BY id DESC"
$result = mysql_query($qry) or die(mysql_error()); 

Can someone help me create a secure mysql statement using input from url parameters (as above) that is prepared?

BONUS: Prepared statements are suppose to increase speed as well. Will it increase overall speed if I only use a prepared statement three or four times on a page?

Solution 1:

Here's an example using mysqli (object-syntax - fairly easy to translate to function syntax if you desire):

$db = new mysqli("host","user","pw","database");
$stmt = $db->prepare("SELECT * FROM mytable where userid=? AND category=? ORDER BY id DESC");
$stmt->bind_param('ii', intval($_GET['userid']), intval($_GET['category']));
$stmt->execute();

$stmt->store_result();
$stmt->bind_result($column1, $column2, $column3);

while($stmt->fetch())
{
    echo "col1=$column1, col2=$column2, col3=$column3 \n";
}

$stmt->close();

Also, if you want an easy way to grab associative arrays (for use with SELECT *) instead of having to specify exactly what variables to bind to, here's a handy function:

function stmt_bind_assoc (&$stmt, &$out) {
    $data = mysqli_stmt_result_metadata($stmt);
    $fields = array();
    $out = array();

    $fields[0] = $stmt;
    $count = 1;

    while($field = mysqli_fetch_field($data)) {
        $fields[$count] = &$out[$field->name];
        $count++;
    }
    call_user_func_array(mysqli_stmt_bind_result, $fields);
}

To use it, just invoke it instead of calling bind_result:

$stmt->store_result();

$resultrow = array();
stmt_bind_assoc($stmt, $resultrow);

while($stmt->fetch())
{
    print_r($resultrow);
}

Solution 2:

You can write this instead:

$qry = "SELECT * FROM mytable where userid='";
$qry.= mysql_real_escape_string($_GET['userid'])."' AND category='";
$qry.= mysql_real_escape_string($_GET['category'])."' ORDER BY id DESC";

But to use prepared statements you better use a generic library, like PDO

<?php
/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('SELECT * FROM mytable where userid=? and category=? 
                      order by id DESC');
$sth->execute(array($_GET['userid'],$_GET['category']));
//Consider a while and $sth->fetch() to fetch rows one by one
$allRows = $sth->fetchAll(); 
?>

Or, using mysqli

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$category = $_GET['category'];
$userid = $_GET['userid'];

/* create a prepared statement */
if ($stmt = mysqli_prepare($link, 'SELECT col1, col2 FROM mytable where 
                      userid=? and category=? order by id DESC')) {
    /* bind parameters for markers */
    /* Assumes userid is integer and category is string */
    mysqli_stmt_bind_param($stmt, "is", $userid, $category);  
    /* execute query */
    mysqli_stmt_execute($stmt);
    /* bind result variables */
    mysqli_stmt_bind_result($stmt, $col1, $col2);
    /* fetch value */
    mysqli_stmt_fetch($stmt);
    /* Alternative, use a while:
    while (mysqli_stmt_fetch($stmt)) {
        // use $col1 and $col2 
    }
    */
    /* use $col1 and $col2 */
    echo "COL1: $col1 COL2: $col2\n";
    /* close statement */
    mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($link);
?>

Solution 3:

I agree with several other answers:

• PHP's ext/mysql has no support for parameterized SQL statements.
• Query parameters are considered more reliable in protecting against SQL injection issues.
• mysql_real_escape_string() can also be effective if you use it correctly, but it's more verbose to code.
• In some versions, international character sets have cases of characters that are not escaped properly, leaving subtle vulnerabilities. Using query parameters avoids these cases.

You should also note that you still have to be cautious about SQL injection even if you use query parameters, because parameters only take the place of literal values in SQL queries. If you build SQL queries dynamically and use PHP variables for the table name, column name, or any other part of SQL syntax, neither query parameters nor mysql_real_escape_string() help in this case. For example:

$query = "SELECT * FROM $the_table ORDER BY $some_column"; 

Regarding performance:

• The performance benefit comes when you execute a prepared query multiple times with different parameter values. You avoid the overhead of parsing and preparing the query. But how often do you need to execute the same SQL query many times in the same PHP request?
• Even when you can take advantage of this performance benefit, it is usually only a slight improvement compared to many other things you could do to address performance, like using opcode caching or data caching effectively.

• There are even some cases where a prepared query harms performance. For example in the following case, the optimizer can't assume it can use an index for the search, because it must assume the parameter value might begin with a wildcard:

  SELECT * FROM mytable WHERE textfield LIKE ?