Sql injection attack , how weak is your application ....

we've been working on this new version of the big website for more than one and a half year. We all know the potential bug in out code, so we need to be careful , careful and more careful.....

Recently when i went through the code, i run into one snippet which caught my eyes:

$sql = 'UPDATE user SET password="'.$password.'" WHERE email="'.$email.'" ';
$stmt = $con->prepareStatement($sql);

this a naked sql, which just update the password for the user's email access. No wrong with it, it works fine.
But where the magic comes is when we choose a password like 6" -- " , all the user's password was changed by me.
This is so called SQL injection attack, (SQL 注入攻击) ，Let's assume we have 1 million user in our website. Now i can change all these 1 million user's password as i want. even more , i can do anything with the db operation.

the sense behind it is the '--' which would be considered as comment character in db operation. anything following '--' would be ignored, other words: anything before it would be executed....;)

so.... how to avoid it?

use this:

$sql = 'UPDATE user SET password= ? WHERE email= ? ';
$stmt = $con->prepareStatement($sql);
$stmt->setString(1, $password);
$stmt->setString(2, $email);

with this, the prepared statement and positional parameter will handle '--', and by pass this attack.

Conclusion :

never use the naked sql by yourself. use OR mapping or prepared statement with named or positional parameter instead.

sql注入攻击，改变密码