Detailed explanation and prevention of SQL injection in php

One is that the input data is not filtered (filtered input), and the other is that the data sent to the database is not escaped (escaped output). These two important steps are indispensable and require special attention to reduce program errors.
For attackers, conducting SQL injection attacks requires thinking and experimenting. It is very necessary to conduct well-founded reasoning about the database solution (assuming of course that the attacker cannot see your source program and database solution). Consider the following Simple login form:

Copy code The code is as follows:

As an attacker, he will start by guessing the query statement to verify the username and password. By looking at the source files, he can start guessing your habits.
For example, naming conventions. It is usually assumed that the field names in your form are the same as the field names in the data table. Of course, ensuring they are different is not necessarily a reliable security measure.
For the first guess, you will generally use the query in the following example:

Copy the code The code is as follows:

< ;?php
$password_hash = md5($_POST['password']);

$sql = "SELECT count(*)
FROM users
WHERE username = '{$_POST ['username']}'
AND password = '$password_hash'";
?>

Using the MD5 value of the user password was originally a common practice, but now it is not Not particularly safe anymore. Recent research shows that the MD5 algorithm is flawed, and the large number of MD5 databases reduces the difficulty of MD5 reverse cracking. Please visit http://md5.rednoize.com/ to view the demo (sic. Research by Professor Wang Xiaoyun of Shandong University shows that MD5 "collision" can be quickly found, that is, two different files and words that can produce the same MD5 value. String. MD5 is a message digest algorithm, not an encryption algorithm, and reverse cracking is out of the question. However, according to this result, it is dangerous to use md5 directly in the above special case.)
The best protection method is to append a string of your own definition to the password, for example:

Copy the code The code is as follows:

$salt = 'SHIFLETT';
$password_hash = md5($salt . md5($_POST['password'] . $salt));
?> ;

Of course, attackers may not be able to guess right the first time, and they often need to do some experiments. A better way to experiment is to enter single quotes as the username, because this may expose some important information. Many developers call the function mysql_error() to report the error when an error occurs during Mysql statement execution. See the example below:

Copy code The code is as follows:

mysql_query($sql) or exit(mysql_error());
?>

While this method is useful in development, it can expose important information to an attacker. If the attacker uses single quotes as the username and mypass as the password, the query statement will become:

Copy code The code is as follows:

$sql = "SELECT *
FROM users
WHERE username = '''
AND password = 'a029d0df84eb5549c641e04a9ef389e5'";
?>

When the statement is sent to MySQL, the system will display the following error message:

Copy code The code is as follows:

You have an error in your SQL syntax. Check the manual that corresponds to your
MySQL server version for the right syntax to use near 'WHERE username = ''' AND
password = 'a029d0df84eb55

Without any effort, the attacker already knows the two field names (username and password) and the order in which they appear in the query. In addition, the attacker also knows that the data is not filtered correctly (the program does not prompt illegal user names) and escaped (a database error occurs), and the format of the entire WHERE condition is also exposed, so that the attacker can try to manipulate There are records that match the query.
At this point, the attacker has many options. One is to try to fill in a special username so that the query can get a match regardless of whether the username and password match:

Copy code The code is as follows:

myuser' or 'foo' = 'foo' --

Assuming that mypass is used as the password, the entire query will become:

Copy code The code is as follows:

< ?php

$sql = "SELECT *
FROM users
WHERE username = 'myuser' or 'foo' = 'foo' --
AND password = 'a029d0df84eb5549c641e04a9ef389e5'" ;

?>

Fortunately, SQL injection is easy to avoid. As mentioned before, you have to insist on filtering input and escaping output.
Although neither step can be omitted, implementing one of them can eliminate most SQL injection risks. If you just filter the input without escaping the output, you are likely to encounter database errors (legal data may also affect the correct format of the SQL query), but this is also unreliable, and legal data may also change the behavior of the SQL statement. On the other hand, if you escape the output without filtering the input, you can ensure that the data will not affect the format of the SQL statement, and also prevent many common SQL injection attack methods.
Of course, you still have to stick to using these two steps at the same time. How you filter the input depends entirely on the type of input data (see examples in Chapter 1), but escaping the output data for sending to the database simply uses the same function. For MySQL users, you can use the function mysql_real_escape_string( ):

Copy the code The code is as follows:

$clean = array();
$mysql = array();

$clean['last_name'] = "O'Reilly";
$mysql['last_name'] = mysql_real_escape_string( $clean['last_name']);

$sql = "INSERT
INTO user (last_name)
VALUES ('{$mysql['last_name']}')";
?>

Try to use escape functions designed for your database. If not, using the addslashes() function is ultimately a better approach.
When all data used to build a SQL statement is properly filtered and escaped, the risk of SQL injection is actually avoided. If you are using a database operation class that supports parameterized queries and placeholders (such as PEAR::DB, PDO, etc.), you will have an extra layer of protection. See the example below using PEAR::DB:

Copy the code The code is as follows:

$sql = 'INSERT
INTO user (last_name)
VALUES (?)';
$dbh->query($sql, array($clean['last_name']));
?>

Since the data in the above example cannot directly affect the format of the query statement, the risk of SQL injection is reduced. PEAR::DB will automatically escape according to your database's requirements, so you only need to filter the output.
If you are using parameterized query statements, the input content will only be processed as data. This eliminates the need for escaping, although you might consider it a necessary step if you wish to stick to the escaping output habit. In fact, whether to escape at this time will basically have no impact, because there are no special characters that need to be converted at this time. Parameterized queries provide powerful protection for your program when it comes to preventing SQL injection.
Note: Regarding SQL injection, it must be said that most virtual hosts now turn on the magic_quotes_gpc option. In this case, all client GET and POST data will automatically be processed by addslashes, so at this time, the string SQL injection of values is not feasible, but SQL injection of numeric values should be prevented, such as using functions such as intval() for processing. But if you are writing general software, you need to read the magic_quotes_gpc of the server and process it accordingly.

Detailed explanation and prevention of SQL injection in php_PHP tutorial