PHP mysql prevent SQL injection detailed description

To prevent SQL injection I have to deal with the data from the SQL statements to the PHP tutorial get post and so on, we mainly talk about the PHP and MySQL tutorial SQL statements on the processing method, may ignore the problem.

Look at this example:

　　

supposed input

$name = "Ilia"; Delete from users; ";

mysql_query ("SELECT * from Users where name= ' {$name} '");

It is clear that the final database tutorial executes the following commands:

SELECT * from users where Name=ilia deletes from users

This has disastrous consequences for the database-all records have been deleted.

However, if you use the database is MySQL, then fortunately, the mysql_query () function does not allow the direct execution of such operations (not a single line to do multiple statement operations), so you can rest assured. If you use a database that is SQLite or PostgreSQL, support such a statement, then you will face extinction.

As mentioned above, SQL injection is primarily to commit unsafe data to the database for attack purposes. In order to prevent SQL injection attacks, PHP has a function to handle the input string, you can at the lower level of the input on the security of the initial treatment, that is, magic quotes. (PHP.ini MAGIC_QUOTES_GPC). If the MAGIC_QUOTES_GPC option is enabled, single quotes, double quotes, and other characters in the string you enter will be automatically preceded by a backslash.

However, Magic quotes is not a common solution that does not mask all potentially dangerous characters, and magic quotes is not enabled on many servers. So we also need to use a number of other ways to prevent SQL injection.

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

Look at this code:

　　

If the Magic quotes function is enabled

if (GET_MAGIC_QUOTES_GPC ()) {

$name = Strips Tutorial Lashes ($name);

}else{

$name = mysql_real_escape_string ($name);

}

mysql_query ("SELECT * from Users where name= ' {$name} '");

Note that before we use the functionality of the database, we need to determine if the magic quotes is open, as in the example above, otherwise two repeat processing will be wrong. If MQ is enabled, we need to remove the addition to get the real data.

In addition to preprocessing data in the above string form, it is also necessary to store binary data in the database to be preprocessed. Otherwise, data may conflict with the database's own storage format, resulting in database crashes, loss of data records, and even loss of entire library data. Some databases, such as PostgreSQL, provide a function pg_escape_bytea () that is designed to encode binary data, which can encode data similar to Base64 's.

Such as:

　

//For Plain-text data use:

pg_escape_string ($regular _strings);

//For binary data use:

Pg_escape_bytea ($binary _data);