How to create a secure mysql prepared statement in php?

by

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

More Related Contents:

  1. How Secure Is This Login System? (Using Cookies In PHP)
  2. How can I prevent SQL injection in PHP?
  3. SQL injection that gets around mysql_real_escape_string()
  4. Is “mysqli_real_escape_string” enough to avoid SQL injection or other SQL attacks?
  5. Reference: What is a perfect code sample using the MySQL extension? [closed]
  6. How do I use password hashing with PDO to make my code more secure? [closed]
  7. What does it mean to escape a string?
  8. SQL injections in ADOdb and general website security
  9. PHP MySQLI Prevent SQL Injection [duplicate]
  10. Why is using a mysql prepared statement more secure than using the common escape functions?
  11. Are mysql_real_escape_string() and mysql_escape_string() sufficient for app security?
  12. How to best store user information and user login and password
  13. how safe are PDO prepared statements
  14. “slash before every quote” problem [duplicate]
  15. Hiding true database object ID in url’s
  16. a better approach than storing mysql password in plain text in config file?
  17. Best way to connect to MySQL with PHP securely [duplicate]
  18. Improve password hashing with a random salt
  19. Php mysql tabs don’t output [closed]
  20. Update Mysql except when field not null
  21. jQuery Validate remote method usage to check if username already exists
  22. mysql_fetch_array return only one row
  23. Insert multiple rows with PDO prepared statements
  24. mysql query result in php variable
  25. mysql_real_escape_string is undefined
  26. How do detect that transaction has already been started?
  27. CakePHP Database connection “Mysql” is missing, or could not be created
  28. MySQL Alter Table Add Field Before or After a field already present
  29. mysql error ‘TYPE=MyISAM’
  30. UNION syntax in Cakephp

Leave a Comment