The difference between emulated prepares and direct prepare calls?

Question

Is database still vulnerable to SQL injection with prepare statements, like in the following example (I'm using PDO and php version 5.3):

  $unsafe = $_POST['user_input'];

  $stmt = $db->prepare("INSERT INTO table (column) VALUES ('" . $unsafe . "')");

and is there a worth mentioning difference in the two examples?

  $stmt = $db->prepare('SELECT * FROM table WHERE name = :name');

  $stmt->execute(array(':name' => $name));

and

    $stmt = $db->prepare('SELECT * FROM table WHERE name = :name');
    $stmt->bind_param(':name', $name);

    $stmt->execute();

What type of parameters can be used in method execute?

possible duplicate of [PDO MySQL: Use PDO::ATTR\_EMULATE\_PREPARES or not?](http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not) — mario, Mar 14 '13 at 23:20

score 1 · Accepted Answer · answered Mar 15 '13 at 00:26

Though it's not entirely clear what your actual question is, let me say this:

The first snippet you posted is right out. There is not a single valid argument for you to use a deprecated extension (or an extension that is being deprecated) like mysql_* at all. Forget about all those pesky mysql_connect calls all together.
looking at the other two snippets I'm guessing you're using PDO, which does support prepare-emulation. In fact, it emulates prepares by default. If, however the third snippet is a mysqli_* snippet, what's the problem? unless you're running MySQL 3.x you're all right there.

The difference between emulated prepares and direct prepare calls is that emulation might spot some syntax errors without the MySQL server being involved, thus being more efficient. If the your MySQL server doesn't support prepared statements, however, I'm pretty sure PDO takes care of that for you, so you should be all right there. Don't know about mysqli_*, though. All things considered, I'm in favour of the second snippet anyhow. It's less code, does the same thing as the third snippet and allows for tidy, easy to maintain code.

On the question of "What type of parameters can be used in method execute", You can consult the doc pages, in case of PDO it's either no params, or an array as you can see here: bool PDOStatement::execute ([ array $input_parameters ] )
In case of mysqli_execute, it depends on your using the OO or procedural style, as shown in the docs:
bool mysqli_stmt::execute ( void ) for OO
bool mysqli_stmt_execute ( mysqli_stmt $stmt ) for procedural style

Edited a little bit to clarify some things( I copy/pasted first snippet). Thx for the answer! — troks, Mar 15 '13 at 12:51

score 0 · Answer 2 · edited May 23 '17 at 12:11

Is database still vulnerable to SQL injection with prepare statements, like in the following example

Why? Sure. Because there are no prepared statements actually used in this example.

The difference between emulated prepares and direct prepare calls?

There is no difference in terms of security - either way is secure.
However, there could be an inconvenience described below

Errr... It just came to my mind that you may be have wrong idea of what emulated prepares is.
The first code snippet in your question is not a prepared statement at all, neither emulated nor natural - but old good extrapolation with all the dangers behind.
While emulated prepares are stands for just the way PDO operate your query, explained here

What type of parameters can be used in method execute?

That's quite obvious - all parameters treated as strings by default.

So, your query is invulnerable either way, but there could be just some inconvenience if emulation is ON - if there are placeholders in the LIMIT clause, you have to bind parameters explicitly, not pass them into execute, setting INT type for them.
But if emulation is disabled, mysql can sort all the parameters all right, and you can send array into execute() all the way.

The difference between emulated prepares and direct prepare calls?

2 Answers2