MySQL binding parameter Bind_param principle and anti-SQL injection

Suppose we have a row in our user table. The user name is username. The value is AAA. The password field is pwd. The value is pwd.

Let's simulate a user login process.

<?php    $username = "AAA";    $pwd = "pwd";    $sql = "SELECT * FROM table WHERE username = ' {$username} ' and pwd = ' {$pwd} '";    Echo $sql; Output  SELECT * FROM table WHERE username = ' aaa ' and pwd = ' pwd '    ?>  

　

This executes the SQL statement. It's obvious that you can find something. Returns this column of the user. Login Successful!!
And then I changed it. Change the password. A random value. I changed to PPP.

<?php    $pwd = ' PPP ';    $sql = "SELECT * FROM table WHERE username = ' {$username} ' and pwd = ' {$pwd} '";    Echo $sql; Output  SELECT * FROM table WHERE username = ' aaa ' and pwd = ' PPP '    ?>   

　　

This is obvious. If you go to execute this SQL statement: You can't find anything. That is, the password is wrong. Login failed!!
But some people are always dishonest. They will do everything possible to log in illegally. The so-called illegal is to log in when he does not know the user name password. and login successful.
So what are the principles they are doing?? In fact, the principle is to use SQL statements. The powerful SQL statement also brought us a lot of trouble.
Let me give you the simplest example. The SQL keyword we're going to use is or
or the code above. We just need to change the password.

<?php $username = "AAA"; $pwd = "FDSAFDA ' or ' 1 ' = ' 1"; The previous password is blind to fill. Later with or keyword: It means that it doesn't matter what the code does $sql = "select * FROM table WHERE username = ' {$username} ' and pwd = ' {$pwd} '"; Echo $sql; Output SELECT * FROM table WHERE username = ' aaa ' and pwd = ' FDSAFDA ' or ' 1 ' = ' 1 '?>

　　

Execute this SQL statement: Terrible things have happened. Can query to this line of data: The login was successful.
What a horrible thing it is.

SQL Injection demo tutorial, see blog: http://blog.csdn.net/wusuopubupt/article/details/8818996

PHP in order to solve this problem. Magic_quotes state. Is that PHP will automatically filter the Get.post and so on.
Digression. Practice proves that this thing is deformed. Most programs have to spend a lot of code to judge this feature.
There is no such thing in Java. So how to prevent this SQL injection in Java??

A class named PreparedStatement is provided in the Java SQL package.
This class is the binding parameter I want to say!
What is a binding parameter?? I continue to give you an example. (I use PHP for example)

<?php $username = "AAA"; $pwd = "PWD"; $sql = "SELECT * FROM table WHERE username =? and pwd =? "; Bindparam ($sql, 1, $username, ' STRING '); In the form of a string. In the place of the first question mark, bind $username this variable bindparam ($sql, 2, $pwd, ' STRING '); In the form of a string. In the place of the second question mark, bind $pwd the variable echo $sql;?>

　　

Of course. You don't know what to export. It's even more impossible to know what the benefits of binding parameters are! What is the advantage of doing this? I don't know what bindparam this function actually does.
I'll simply write down this function:

<?php  /**   * Simulates a simple binding parameter procedure   *   * @param string $sql    SQL statement   * @param int $location  question mark position   * @param mixed $var     the variable to replace   * @param string $type   the type of substitution */   $times = 0;  It is important to note that because you want to "really" change the value of $sql, use the reference function Bindparam (& $sql, $location, $var, $type) {      global $times;      OK type      switch ($type) {          //string default          :                    //Use String type case          ' string ':              $var = addslashes ($ var);  Escape              $var = "'". $var. "'";      Plus single quotation marks. The insertion of a string in an SQL statement must be a single quote break              ;          Case ' INTEGER ': Case          ' INT ':              $var = (INT) $var;         Cast          to int//can also add more types:      }      Find the location of the question mark for      ($i =1, $pos = 0; $i <= $location; $i + +) {          $pos = Strpos ($sql, '? ', $pos + 1);      }      Replace question mark      $sql = substr ($sql, 0, $pos). $var. substr ($sql, $pos + 1);}  

　

Note: Because you need to know the number of times to remove the question mark ... So I used a global to solve it. If you put it in a class it's very easy. Get a private property to

Through this function above. We know. The anti-injection method of binding parameters is actually escaped by escaping . It's just for variables.
Let's do an experiment:

<?php $times = 0; $username = "AAAA"; $pwd = "123"; $sql = "SELECT * FROM table WHERE username =? and pwd =? "; Bindparam ($sql, 1, $username, ' STRING '); In the form of a string. In the place of the first question mark, bind $username this variable bindparam ($sql, 2, $pwd, ' INT '); In the form of a string. In the place of the second question mark, bind $pwd this variable echo $sql; Output SELECT * FROM table WHERE username = ' aaaa ' and pwd = 123?>

　　

You can see that. A very formal SQL statement was generated. That's good. Let's try the situation that was just injected.

<?php  $times = 0;  $username = "AAA";  $pwd = "FDSAFDA ' or ' 1 ' = ' 1";  $sql = "SELECT * FROM table WHERE username =? and pwd =? ";  Bindparam ($sql, 1, $username, ' STRING ');  In the form of a string. In the place of the first question mark, bind $username this variable  bindparam ($sql, 2, $pwd, ' STRING ');       In the form of a string. In the place of the second question mark, bind $pwd this variable  echo $sql;//Output  SELECT * FROM table where username = ' aaa ' and pwd = ' fdsafda\ ' or \ ' 1\ ' =\ ' 1 '  

You can see that. The injection inside the PD has been escaped. As a complete string. In that case, it can't be injected.

Go to http://blog.csdn.net/wusuopubupt/article/details/9668501

MySQL binding parameter Bind_param principle and anti-SQL injection