plain mysql prepare statement prevent injection attack?

2018-06-05 06:20:05

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?

[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.

链接地址: http://www.djcxy.com/p/16758.html

上一篇: 用PDO解决SQL注入问题