MySQL and SQL Injection

SQL injection security can occur if you get user-entered data from a Web page and insert it into a MySQL database.

This section will show you how to prevent SQL injection and use scripts to filter the characters injected into SQL.

SQL injection, by inserting a SQL command into a Web form to submit or entering a query string for a domain name or page request, eventually achieves a malicious SQL command that deceives the server.

We should never trust the user input, we must assume that the user input data is not safe, we all need to the user input data to filter processing.

In the following instance, the user name entered must be a combination of letters, numbers, and underscores, with a user name length between 8 and 20 characters:

if (Preg_match ("/^\w{8,20}$/", $_get[' username '], $matches)) {   $result = mysql_query ("SELECT * from Users                           WHERE Username= $matches [0] ");} else {   echo "username input exception";}

Let's look at the SQL that appears when no special characters are filtered:

The settings $name Insert the SQL statement we don't need $name = "Qadir"; DELETE from users; "; mysql_query ("SELECT * from users WHERE name= ' {$name} '");

In the above injection statement, we did not filter the $name variables, we inserted the SQL statements we did not need, and all the data in the users table would be deleted.

mysql_query () in PHP is not allowed to execute multiple SQL statements, but SQLite and PostgreSQL can execute multiple SQL statements at the same time, so we need to rigorously validate the data of these users.

To prevent SQL injection, here are a few important points to note:

• 1. Never trust the user's input. The user's input can be verified by regular expressions, or by limiting the length, by converting the single quotation mark and the double "-".
• 2. Never use dynamically assembled SQL, either using parameterized SQL or directly using stored procedures for data query access.
• 3. Never use a database connection with administrator rights, and use a separate limited database connection for each app.
• 4. Do not store confidential information directly, encrypt or hash out passwords and sensitive information.
• 5. Applied exception information should give as few hints as possible, preferably using a custom error message to wrap the original error message
• 6.sql injection detection method generally take the aid software or website platform to detect, software generally use SQL injection detection Tool Jsky, website platform has billion think website security platform detection tools. Mdcsoft scan and so on. The use of mdcsoft-ips can effectively protect against SQL injection, XSS attacks and so on.

Prevent SQL injection

In scripting languages, such as Perl and PHP, you can escape the data entered by the user to prevent SQL injection.

PHP's MySQL extension provides the mysql_real_escape_string () function to escape special input characters.

if (GET_MAGIC_QUOTES_GPC ()) {  $name = stripslashes ($name);} $name = mysql_real_escape_string ($name); mysql_query ("Select * from users WHERE name= ' {$name} '");

Injection in a LIKE statement

Like query, if the user entered the value of "_" and "%", then this situation will occur: the user is only want to query "Abcd_", query results are "abcd_", "ABCDE", "ABCDF" and so on, the user to query "30%" (note: 30%) When the problem occurs.

In PHP scripts we can use the addcslashes () function to handle this situation, as in the following example:

$sub = Addcslashes (mysql_real_escape_string ("%something_"), "%_");//$sub = = \%something\_mysql_query ("SELECT * FROM Messages WHERE subject like ' {$sub}% ' ");

The Addcslashes () function adds a backslash before the specified character.

Syntax format:

Addcslashes (String,characters)

Parameters	Description
String	Necessary. Specifies the string to check.
Characters	Optional. Specifies the range of characters or characters affected by addcslashes ().

Specific applications can be viewed: PHP addcslashes () function

Appendix

PHP mysql_real_escape_string () function definition and usage

The mysql_real_escape_string () function escapes special characters in strings used in SQL statements.

The following characters are affected:

• \x00
• \ n
• \ r
• \
• ‘
• "
• \x1a

If successful, the function returns the escaped string. If it fails, it returns false.

Grammar

Mysql_real_escape_string (string,connection)

Parameters	Description
String	Necessary. Specifies the string to be escaped.
Connection	Optional. Specify MySQL connection. If not specified, the previous connection is used.

Description

This function escapes the special characters in the string and takes into account the current character set of the connection, so it can be used safely for mysql_query ().

Hints and Notes

Tip: You can use this function to prevent database attacks.

Example Example 1

<?php$con = mysql_connect ("localhost", "Hello", "321"), if (! $con)  {die  (' Could not connect: '. Mysql_error () );  mysql_real_escape_string($user)mysql_real_escape_string($pwd); $sql = "SELECT * from Users whereuser= '". $user. "' and password= '". $pwd. "'"//More Code mysql_close ($con);? >

Example 2

Database attacks. This example shows what happens if we do not apply the mysql_real_escape_string () function to the username and password:

<?php$con = mysql_connect ("localhost", "Hello", "321"), if (! $con)  {die  (' Could not connect: '. Mysql_error () );  } $sql = "SELECT * from Userswhere user= ' {$_post[' user '} ' and password= ' {$_post[' pwd '} '"; mysql_query ($sql);//Do not check username and password Can be any user input, such as: $_post[' user '] = ' john '; $_post[' pwd '] = ' OR ' = ' ";//Some code ... mysql_close ($con);? >

Then the SQL query will be like this:

SELECT * from Userswhere user= ' John ' and password= ' OR ' = '

This means that any user can log in without entering a valid password.

Example 3

The right way to prevent database attacks:

<?phpfunction Check_input ($value) {//Strip Slash if (GET_MAGIC_QUOTES_GPC ())  {  stripslashes($value) ;  } If it is not a number, quote if (!is_numeric ($value))  {  mysql_real_escape_string($value) . "'";  } return $value;} $con = mysql_connect ("localhost", "Hello", "321"), if (! $con)  {die  (' Could not connect: '. Mysql_error ());  } Secure Sql$user = check_input ($_post[' user "), $pwd = Check_input ($_post[' pwd '); $sql =" SELECT * from Users whereuser= $u Ser and password= $pwd "; mysql_query ($sql); Mysql_close ($con);? >

PHP addcslashes () function definition and usage

The Addcslashes () function adds a backslash before the specified character.

Grammar

Addcslashes (string,characters)

Parameters	Description
String	Necessary. Specifies the string to check.
Characters	Optional. Specifies the range of characters or characters affected by addcslashes ().

Hints and Notes

Note: Be careful when applying addcslashes () to 0,r,n and T. In PHP, \0,\r,\n and \ t are pre-defined escape sequences.

Example 1

In this case, we're going to add a backslash to a specific character in the string:

addcslashes($str,‘m‘);addcslashes($str,‘J‘);?>

Output:

Hello, my name is John Adams.hello, \my Na\me is John Ada\ms. Hello, my name is \john Adams.

Example 2

In this case, we're going to add a backslash to a range of characters in the string:

addcslashes($str,‘A..Z‘)addcslashes($str,‘a..z‘)addcslashes($str,‘a..h‘);? >

Output:

Hello, my name is John Adams.\hello, my name is \john \adams.h\e\l\l\o, \m\y \n\a\m\e \i\s j\o\h\n A\d\a\m\s.h\ello, my n\ Am\e is Jo\hn a\d\ams.

MySQL and SQL Injection