MySQL and SQL injection and prevention methods _mssql

SQL injection is a query string that inserts or enters a domain name or page request by inserting a SQL command into a Web form, and finally achieves a malicious SQL command that deceives the server.

We should never trust user input, we must assume that the user input data is unsafe, we all need to user input data filtering processing.

1. In the following example, the username entered must be a combination of letters, numbers, and underscores, and the user name is 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:

Setup $name SQL statements that we don't need
$name = "Qadir"; DELETE from users; ";
mysql_query ("SELECT * from users WHERE name= ' {$name} '");

In the above injection statements, we do not filter $name variables, $name Insert the SQL statement we do not need, will delete all the data in the Users table.

2. 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, we need to note the following points:

1. Never trust user input. The user's input is validated by the regular expression, or by the limit length, by the single quotation mark and the double "-".
2. Never use dynamic assembly SQL, you can use parameterized SQL or directly use stored procedures for data query access.
3. Never use a database connection with administrator privileges, and use a separate, limited database connection for each application.
4. Do not store confidential information directly, encrypt or hash out passwords and sensitive information.
5. The application of the exception information should give as little hint as possible, it is best to use the custom error message to the original error message packaging
6.sql injection detection methods generally take the auxiliary software or website platform to detect, the software is generally using SQL injection detection Tool Jsky, the website platform has billion-SI Web site security platform detection tools. Mdcsoft scan and so on. The use of mdcsoft-ips can effectively protect against SQL injection, XSS attack and so on.

3. Prevent SQL injection

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

The 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} '");

Injection in a 4.Like statement

Like query, if the user entered a value of "_" and "%", this is the case: the user would have just wanted to query "Abcd_", the query results are "abcd_", "ABCDE", "ABCDF" and so on, the user to query "30%" (note: 30%) When the problem will also occur.

In the PHP script we can use the addcslashes () function to handle the above situation, 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 required. Specify the string to check.
Characters optional. Specify the character or range of characters affected by addcslashes ().