How to Create a Secure MySQL Prepared Statement in PHP

How to create a secure mysql prepared statement in php?

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

PHP & MySQL: Creating your own Prepared statement without using MySQLi and/or PDO

There are two types of prepared statements, the emulated prepared statements and the native prepared statements. What you are doing is emulating the prepared statements.

---

1.How to create a MySQL prepared statement?

In php level, you could only emulate the prepared statement by replacing the placeholder with secure values. (Ex: quote the string, escape the special char, and so on...)

2.Why it is secure?

Because the prepared statements prevent the sql injection.

---

PS:
PDO has the option of PDO::ATTR_EMULATE_PREPARES, which enables or disables emulation of prepared statements.

PHP - secure PDO prepared statement with an unknown number of parameters

For such problems, the database query builder solutions would be better.

Returning to the question: you know the number of parameters, so you can create query template.

<?php
$wq = $pq = [];
$allowed_keys = ['name', 'occupation', '...'];

foreach ($arguments AS $key => $value) {
  if(in_array($key, $allowed_keys))
    $pq[] = "$key = :$key" ;
  else throw new Exception('Go away hacker!');
}
foreach ($filter AS $key => $value)
  if(in_array($key, $allowed_keys))
    $wq[] = "$key = :$key" ;

$q = "UPDATE $table SET ". join(', ', $pq);
if(!empty($wq))
  $q .= 'WHERE '. join(' AND ', $wq);

In results you get query pattern like:

UPDATE workers SET name = :name, occupation = :occupation WHERE employeeId = :employeeId;

Such folding is allowed (if you have "secure" keys to these tables)

$allowed_keys or better $allowed_column_names

Secure, extensible database with dynamic prepared statements

My first reaction to your SQL is that you really need to learn how to use JOIN in SQL. The join operation is really fundamental to SQL and relational data. Using only subqueries in lieu of JOIN is like using another programming language, but refusing to use a while() loop. Sure, you can do it, but why?

$sql = "SELECT * FROM item WHERE cost = :cost
AND id IN (SELECT item_id FROM item_color 
WHERE color_id IN (SELECT id FROM color WHERE val = :color));";

Should be

$sql = "
 SELECT i.id, i.name, i.cost, c.color 
 FROM item AS i
 INNER JOIN item_color AS ic ON i.id = ic.item_id
 INNER JOIN color AS c ON c.id = ic.color_id
 WHERE i.cost = :cost AND c.val = :color";

Any reference or tutorial on SQL covers joins.

As for your question about safety, yes—using query parameters is safe with respect to SQL Injection. By making your base query hard-coded and separating the dynamic parts into parameters, you eliminate any chance for unsafe data to change the parsing of your SQL query.

You might like my presentation SQL Injection Myths and Fallacies (video: https://www.youtube.com/watch?v=VldxqTejybk).

Your requirements make me think you'd be better off using a document database like MongoDB, where you can add attributes to any document. It doesn't mean you don't still have to be careful about database design, but it gives you the opportunity to add attributes after design more easily.

Why is using a mysql prepared statement more secure than using the common escape functions?

An important point that I think people here are missing is that with a database that supports parameterized queries, there is no 'escaping' to worry about. The database engine doesn't combine the bound variables into the SQL statement and then parse the whole thing; The bound variables are kept separate and never parsed as a generic SQL statement.

That's where the security and speed comes from. The database engine knows the placeholder contains data only, so it is never parsed as a full SQL statement. The speedup comes when you prepare a statement once and then execute it many times; the canonical example being inserting multiple records into the same table. In this case, the database engine needs to parse, optimize, etc. only once.

Now, one gotcha is with database abstraction libraries. They sometimes fake it by just inserting the bound variables into the SQL statement with the proper escaping. Still, that is better than doing it yourself.

---

Related Topics