plain mysql prepare statement prevent injection attack?

Question

Everyone knows that PDO prepare statement helps us prevent SQL injection attack. How about this:

function userQuery($username){
    $mysqli->multi_query("
        PREPARE stmt1 FROM 'SELECT * FROM user WHERE username=?';
        SET @a = '$username';
        EXECUTE stmt1 USING @a
    ");
}
userQuery('Kelvin');

Is this as safe as mysqli or PDO prepare statement?

I'm asking this question because I found these sentences on wiki:

Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

They mention about parameters are transmitted later with different protocol. And I don't really understand this.

How can parameters are transmitted later with different protocol prevent injection attack?

Please think about **how** injection attacks work, and then apply the same logic to your scenario above. — Oliver Charlesworth, Aug 30 '14 at 10:34
I've updated my question. Please let me know if I am still wrong somewhere. — user3925697, Aug 30 '14 at 10:47

score 2 · Accepted Answer · answered Aug 30 '14 at 10:46

2

[I assume that you meant $mysqli is mysqli connection.]

Although the execution of stmt1 (the last of your three queries) is safe, the multi-query function you wrote is very unsafe. Running something like

userQuery("'; delete from user; select * from user where username='");

will actually delete all users from your user table. Assuming $username represents raw user input without proper escaping, the consequences can be catastrophic.

You could possibly improve the above and do the escaping on your own using mysqli::real_escape_string, but there are many more sophisticated ways to do hacks like one above. Prepared statements are all in all a better solution.

answered Aug 30 '14 at 10:46

vch

131
8

Thanks for your answer. BTW, do you mean that it will be safe if I separate my multiple query to individual queries? – user3925697 Aug 30 '14 at 11:04
If you do this (not sure if this would work), your set @a=... will still have thr same properties. If you want to do this securely using mysqli, check out the following answer: [How to create a secure prepared mysql statement in PHP?](http://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php). – vch Aug 30 '14 at 23:26
As for your question update: The way prepared statements work is slightly different to how normal statements work. Unlike your query, which is transmitted to the server together with the parameters injected by you, then compiled, executed and discarded, your prepared statement query is first transmitted to the server with parameter placeholders. There it is compiled, still with parameter placeholders only. This statement is then ready to execute. At the point of execution, parameter values are injected into compiled query, so the tricks like the one I demonstrated to you will have no effect. – vch Aug 30 '14 at 23:41
If you want to learn more, please read the [13.5 SQL Syntax for Prepared Statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html). If you need to understand protocol specifics, look at [14.7 Prepared Statements](http://dev.mysql.com/doc/internals/en/prepared-statements.html), one of those two sections should answer all your questions. – vch Aug 30 '14 at 23:44

plain mysql prepare statement prevent injection attack?

1 Answers1