MySQL 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 blog post 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, -}$/", $_get[' username '], $matches)) {$result=mysql_query ("SELECT *  fromUsersWHEREUsername=$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 statements 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()) {  $namestripslashes($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 ().

MySQL SQL Injection