MySQL and SQL injection and prevention methods, mysqlsql

MySQL and SQL injection and prevention methods, mysqlsql

SQL injection is to insert SQL commands into Web forms to submit or input query strings for domain names or page requests, and finally fool the server to execute malicious SQL commands.

We should never trust user input. We must ensure that user input data is insecure and we must filter user input data.

1. In the following example, the user name must be a combination of letters, numbers, and underscores (_), and the length of the user name must be 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 take a look at the SQL statements that occur when no special characters are filtered:

// Set $ name to insert SQL statements that we don't need
$ Name = "Qadir '; delete from users ;";
Mysql_query ("SELECT * FROM users WHERE name = '{$ name }'");

In the preceding injection statement, the $ name variable is not filtered. If an SQL statement is inserted into $ name, all data in the users table is deleted.

2. mysql_query () in PHP cannot execute multiple SQL statements, but SQLite and PostgreSQL can execute multiple SQL statements at the same time, therefore, we need to strictly verify the data of these users.

To prevent SQL injection, pay attention to the following points:

1. Never trust users' input. Validate user input. You can use regular expressions or limit the length. Convert single quotes and double.
2. Never use dynamic assembled SQL statements. You can use parameterized SQL statements or directly use stored procedures for data query and access.
3. Never use a database connection with administrator permissions. Use a database connection with limited permissions for each application.
4. Do not store confidential information directly, encrypt or hash passwords and sensitive information.
5. The application exception information should be given as few prompts as possible. It is best to use custom error information to wrap the original error information.
6. the SQL Injection detection method generally uses auxiliary software or website platform for testing. The software generally uses the SQL Injection detection tool jsky, and the website platform has the Yis website security platform detection tool. Mdcsoft scan. Using MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks and so on.

3. Prevent SQL Injection

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

MySQL extension of PHP 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}'");

4. Injection in the Like statement

If you enter "_" and "%" in the like query, the following error occurs: the user only wants to query "abcd _", the query results contain "abcd _", "abcde", "abcdf", and so on. problems may also occur when users want to query "30%" (Note: 30%.

In the PHP script, we can use the addcslashes () function to handle the above situations, as shown 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)

Parameter description
String is required. Specifies the string to be checked.
Characters is optional. Specifies the character or character range affected by addcslashes.