MySQL SQL injection

SQL injection (SQL injection) attacks are security vulnerabilities in the database layer that occur in an application. In short, a SQL statement is injected into the input string, and if the check is ignored in poorly designed programs, then these injected SQL statements are run by the database server mistaken for a normal SQL statement, and an attacker can execute unplanned commands or access unauthorized data. SQL injection has become the biggest risk for Web applications in the Internet world. We need to take precautions from development, testing and on-line aspects. The following describes the principles of SQL injection and how to prevent SQL injection.

The principle of SQL injection has the following 4 points

1) splicing malicious queries. SQL commands can query, insert, UPDATE, and delete data, separating different commands with semicolon characters.

For example:

SELECT * from users where user_id = $user _id

USER_ID is an incoming parameter, and if "1234;delete from users" is passed in, the final query statement becomes:

SELECT * from users where user_id = 1234; Delete from users

If executed, the data from the user table is deleted.

2) Use annotations to execute illegal commands. In the SQL command, you can insert a comment.

For example:

Select COUNT (*) as ' num ' from Game_score where game_id=24411 and platform_id=11 and version= $version and session_id =

Sessid= ' d7a157-0f-48b6-98-c35592 '

If version contains a malicious string "'-1 ' OR 3 and SLEEP (500)--", then the final query statement will look like this:

Select COUNT (*) as ' num ' from Game_score where game_id=24411 and platform_id=11 and version= '-1 ' OR 3 and SLEEP (500)--'

and session_id = sessid= ' d7a157-0f-48b6-98-c35592 '

The above malicious query just want to run out of system resources, SLEEP (500) will cause SQL to run, if you add a modified, delete data malicious instructions, will cause

Greater destruction.

3) The SQL command is enclosed in single quotation marks for incoming string arguments. If the string itself contains single quotes and is not processed, it may tamper with the original

The role of SQL syntax.

For example:

SELECT * FROM user_name where user_name = $user _name

If user_name is passing in G ' Chen, then the final query will turn into this:

SELECT * FROM user_name where user_name = ' G ' chen '

The above statement will make an error, so the statement is less risky because the syntax error SQL statement will not be executed. But it's also possible to maliciously generate SQL statements without

Any syntax errors and run in a way that you do not expect.

4) Add some additional conditions to the truth expression, changing the execution behavior.

For example:

Update users set USERPASS=SHA2 (' $userpass ') where user_id= $user _id;

If USER_ID is passed in the malicious string "1234 OR TRUE", the final SQL statement will change to the following:

Update users set USERPASS=SHA2 (' 123456 ') where user_id=1234 OR TRUE;

This will change the password for all users.

Here are some ways to avoid SQL injection.

(1) Filter the input content, check the string

You should remove the illegal characters from user input before committing the data to the database. You can use the processing functions provided by the programming language, such as the PHP

Mysql_real_escape_string () to reject, or to define its own processing function for filtering, you can also use regular expressions to match a secure string.

If the value belongs to a specific type or has a contract format, then the validation is validated before stitching the SQL statement. For example, for an incoming

Value, if it can be determined that the integer type, then we have to determine whether it is an integer, not only on the browser side (client), but also on the server side needs to be verified.

(2) Parametric query

Parameterized queries are now considered to be the most effective way to prevent SQL injection attacks. Unlike inserting dynamic content into an SQL statement, the query parameter is done in the

When you are ready to query the statement, use the parameter placeholder where the corresponding parameter is. Then, provide a parameter when executing this pre-prepared query.

In the case of parameterized queries, the database server does not treat the contents of the parameter as part of the SQL instruction, but rather the database completes the SQL

After the instruction is compiled, the parameters are run, so even if the parameters contain destructive instructions, they will not be run by the database.

You can use the mysqli extension or the PDO extension to bind parameters to implement parameterized queries.

The following is an example of an extended binding parameter using Mysqli.

<title> Test parameter Query </title>

<body>

<?php

$host = "127.0.0.1";

$port = 3306;

$socket = "";

$user = "Garychen";

$password = "Garychen";

$dbname = "Employees";

$con = new Mysqli ($host, $user, $password, $dbname, $port, $socket)

Or Die (' Could not connect to the database server '. Mysqli_connect_error ());

Echo ' Connect to employees database successfully ';

echo "<br/>";

echo "Select Departments table using parameter";

echo "<br/>";

$query = "SELECT * FROM departments where Dept_name =?";

if ($stmt = $con->prepare ($query)) {

$stmt->bind_param ("s", $depname);

$depname = "Finance";

$stmt->execute ();

$stmt->bind_result ($field 1, $field 2);

while ($stmt->fetch ()) {

printf ("%s,%s\n", $field 1, $field 2);

echo "<br/>";

}

$stmt->close ();

}

$con->close ();

?>

</body>

The previous example is the preprocessing statement if ($stmt = $con->prepare ($query)), and then the binding parameter uses the Bind_param () method, which has the syntax format as shown below.

BOOL Mysqli_stmt::bind_param (String $types, Mixed & $var 1 [, Mixed &$ ...]

Where types specifies the type of the binding parameter, containing one or more characters. I represents an integer, D is a double, S represents a string, B is a BLOB type,

In this case, S.

However, the binding parameters also have the following limitations.

· You cannot substitute a placeholder "?" for a set of values, for example:

SELECT * FROM Departments WHERE userid in (?);

· The placeholder "?" cannot be used in place of a data table or column name, for example:

SELECT * FROM Departments ORDER by?;

· You cannot replace the placeholder "?" with the SQL keyword, for example:

SELECT * FROM Departments ORDER by Dept_no?;

For Java, JSP development applications, you can also use preprocessing statements and binding parameters in a way to avoid SQL injection.

(3) Safety Test, safety audit

In addition to developing specifications, processes, mechanisms, and appropriate tools are required to ensure code security. We should review the code during the development process, in the test session

Use tools to scan, and periodically scan for security breaches after online. It is generally possible to avoid SQL injection by checking multiple links.

Some people think that stored procedures can avoid SQL injection, stored procedures in the traditional industry used more, for the control of permissions is useful, but if the memory

The stored process uses dynamic query, splicing SQL, then there will be a security risk. Some programming frameworks are also helpful in writing more secure code because it

Provides some functions for working with strings and methods for using query parameters, but you can still write unsafe SQL statements. So ultimately,

From the way of mysqldba cultivation

This article from "Big Plum" blog, declined reprint!

MySQL SQL injection