How to create a secure mysql prepared statement in php?

Question

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?

Answer 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); } 

Answer 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); ?>