PHP and SQL injection attacks

SQL injection attacks are the most common means of hacking websites. If your site does not use strict user input validation, then often vulnerable to SQL injection attacks. SQL injection attacks are usually done by submitting bad data or query statements to the site database, most likely causing records in the database to be exposed, changed, or deleted. Here to talk about SQL injection attacks is how to achieve, but also how to prevent.

Look at this example:

// supposed input

$ name = "ilia '; DELETE FROM users;";

mysql_query ("SELECT * FROM users WHERE name = '{$ name}'");

Obviously the last database to execute the command is:

SELECT * FROM users WHERE name = ilia; DELETE FROM users

This has had catastrophic consequences for the database - all records are deleted.

However, if you are using a MySQL database, it is fine that the mysql_query () function does not allow direct execution of such operations (you can not do more than one statement at a time) so you can rest assured. If you are using a database that is SQLite or PostgreSQL that supports such statements, then you will be drowned.

Mentioned above, SQL injection is mainly to submit unsafe data to the database to achieve the purpose of attack. In order to prevent SQL injection attacks, PHP comes with a function that can handle the input string, you can in the lower level of the input for the security of the initial treatment, which Magic Quotes. (php.ini magic_quotes_gpc). If the magic_quotes_gpc option is enabled, single quotes, double quotes, and some other characters in the input string are automatically preceded by a backslash.

But Magic Quotes is not a very generic solution, it does not mask all potentially dangerous characters, and Magic Quotes are not enabled on many servers. So, we also need to use a variety of other ways to prevent SQL injection.

Many databases provide this input data processing function by themselves. For example, PHP's MySQL operation function has a function called mysql_real_escape_string () that escapes special characters and characters that may cause errors in database operations.

Look at this code:

// If Magic Quotes function is enabled

if (get_magic_quotes_gpc ()) {

$ name = stripslashes ($ name);

} else {

$ name = mysql_real_escape_string ($ name);

}

mysql_query ("SELECT * FROM users WHERE name = '{$ name}'");

Note that before we use the database with the function to determine whether the Magic Quotes open, as in the previous example, or otherwise repeat the process will be wrong. If MQ is enabled, we have to add the removed to get the real data.

In addition to the above string data in the form of pretreatment, the Binary data stored in the database, but also pay attention to pretreatment. Otherwise, the data may conflict with the storage format of the database itself, causing the database to crash, losing data records, or even losing data of the entire library. Some databases, such as PostgreSQL, provide a pg_escape_bytea () function specifically for encoding binary data, which encodes the data in a manner similar to Base64.

Such as:

// for plain-text data use:

pg_escape_string ($ regular_strings);

// for binary data use:

pg_escape_bytea ($ binary_data);

In another case, we also need to adopt such a mechanism. That is the database system itself does not support multi-byte languages ​​such as Chinese, Japanese and so on. Some of these ASCII ranges overlap with the range of binary data.

However, encoding the data will likely result in queries like LIKE abc% failing.