Php mysql_real_escape_string addslashes and mysql bind parameters to prevent SQL injection attacks and realescapestring
Php mysql_real_escape_string addslashes and mysql bind parameters to prevent SQL injection attacks
Php has three methods to prevent SQL injection attacks:
- Use the mysql_real_escape_string Function
- Use the addslashes Function
- Use mysql bind_param ()
This article describes in detail the effects and differences of these three methods in preventing SQL injection attacks.
Mysql_real_escape_string defends against SQL injection attacks
The mysql_real_escape_string () function escapes special characters in strings used in SQL statements.
In some cases, mysql_real_escape_string and mysql_set_charset must be used together, because if encoding is not specified, character encoding may bypass the mysql_real_escape_string function. For example:
$name=$_GET['name'];$name=mysql_real_escape_string($name);$sql="select *from table where name like '%$name%'";
When the input name is name = 41% bf % 27% 20or % 20 sleep % 2810.10% 29% 3d0% 20 limit % 201% 23, the SQL statement output is:
SELECT * FROM table WHERE name LIKE '%41¿\\\' or sleep(10.10)=0 limit 1#%';
At this time, SQL injection attacks are triggered.
The following describes how to prevent SQL injection attacks by using the mysql_real_escape_string function:
<? Phpfunction check_input ($ value) {// remove the slash if (get_magic_quotes_gpc () {$ value = stripslashes ($ value );} // if it is not a number, enclose it with quotation marks/* http://www.manongjc.com/article/1242.html */if (! Is_numeric ($ value) {$ value = "'". mysql_real_escape_string ($ value ). "'";} return $ value;} $ con = mysql_connect ("localhost", "hello", "321"); if (! $ Con) {die ('could not connect :'. mysql_error ();} // SQLmysql_set_charset ('utf-8'); $ user = check_input ($ _ POST ['user']); $ pwd = check_input ($ _ POST ['pwd']); $ SQL = "SELECT * FROM users WHEREuser = $ user AND password = $ pwd"; mysql_query ($ SQL ); mysql_close ($ con);?>
Addslashes protects against SQL injection attacks
Many PHP coders in China are still relying on addslashes to prevent SQL injection (including me). I suggest you strengthen Chinese characters to prevent SQL Injection checks. Addslashes can replace single quotes with 0xbf27, while addslashes only changes 0xbf27 to 0xbf5c27 to a valid multi-byte character, where 0xbf5c is still considered as single quotes, therefore, addslashes cannot be intercepted. Of course, addslashes is not useless. It can be used for processing single-byte strings.
Addslashes will automatically add \ to single quotation marks and double quotation marks, so that we can securely store data into the database without being exploited by hackers. Parameter 'a .. z' defines that all uppercase and lowercase letters are escaped. The Code is as follows:
Echo addcslashes ('foo [] ', 'a. Z'); // output: foo [] $ str = "is your name o 'Reilly? "; // Define a string, including the echo addslashes ($ str) character to be escaped; // output the escaped string
Mysql bind_param () bind parameters to prevent SQL injection attacks
For example:
<? Php $ username = "aaa"; $ pwd = "pwd"; $ SQL = "SELECT * FROM table WHERE username =? AND pwd =? "; BindParam ($ SQL, 1, $ username, 'string'); // In the STRING format. bindParam ($ SQL, 2, $ pwd, 'string'); // bind the $ username variable to the first question mark. bind the $ pwd variable echo $ SQL In the second question mark;?>
You certainly don't know what will be output, and you cannot even know the benefits of binding parameters! What are the advantages of this function? I don't know what the bindParam function actually does.
Below I will briefly write this function:
<? Php/*** simulate simple parameter binding process ** @ param string $ SQL SQL statement * @ param int $ location question mark position * @ param mixed $ var variable * @ param string $ type replacement type */$ times = 0; // note that you need to change the value of $ SQL by referencing the value of function bindParam (& $ SQL, $ location, $ var, $ type) {global $ times; // confirm the type switch ($ type) {// STRING default: // The default STRING type is case 'string': $ var = addslashes ($ var ); // escape $ var = "'". $ var. "'"; // Add single quotes. in SQL statements, single quotation marks (break) must be added to string insertion. ca Se 'integer': case 'int': $ var = (INT) $ var; // convert it to int. // You can also add more types ..} // locate the question mark ($ 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: I have to know the number of times to remove question marks, so I used a global solution. It would be very easy to put it into the class. You can set a private attribute
Through the above function, we know that the anti-injection method for binding parameters is actually carried out through escaping. It is only 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 STRING format. bindParam ($ SQL, 2, $ pwd, 'int'); // bind the $ username variable to the first question mark. bind the $ pwd variable echo $ SQL to the second question mark; // output SELECT * FROM table WHERE username = 'aaa' AND pwd = 123?>
We can see that a very formal SQL statement is generated. Well, now 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 STRING format. bindParam ($ SQL, 2, $ pwd, 'string'); // bind the $ username variable to the first question mark. bind the $ pwd variable echo $ SQL to the second question mark; // output SELECT * FROM table WHERE username = 'aaa' AND pwd = 'fdsafda \ 'or \ '1 \' = \ '1'?>
We can see that the injection in. pwd has been escaped. It is treated as a complete string. In this case, it is impossible to be injected.
Summary:
The preceding three methods can prevent SQL injection attacks, but both the first and second methods have the character encoding vulnerability. Therefore, we recommend that you use the third method in this article.
Thank you for reading this article. I hope it will help you. Thank you for your support for this site!