Php mysql_real_escape_string addslashes and mysql bind parameters to prevent SQL injection attacks and realescapestring

Source: Internet
Author: User
Tags how to prevent sql injection how to prevent sql injection attacks

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:

  1. Use the mysql_real_escape_string Function
  2. Use the addslashes Function
  3. 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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.