PHP+MYSQL website SQL injection attack and defense

Programmers write code with TDD (test-driven development): Before implementing a feature, you write a test case and then write the code to run it through. In fact, when the hacker SQL injection, the same is a TDD process: they will try to let the program error, and then 1.1 points of correction parameters, when the program again run success, injection will then succeed.

OFFENSE:

Suppose you have a script in your program that resembles the following:

$sql = "SELECT ID, title, content from articles WHERE id = {$_get[' id ']}";

The URL for normal access is as follows:

/articles.php?id=123

When a hacker wants to determine whether a SQL injection vulnerability exists, the most common way to do this is to add a single quote after the plastic ID:

/articles.php?id=123 '

Since we do not filter the $_get[' id ' parameter, the error is inevitable and may be similar to the following information:

supplied argument is isn't a valid MySQL result resource ...

This information is enough to explain that there are holes in the script, and we can do something about it:

/articles.php?id=0 Union Select 1,2,3

The reason why select 1,2,3 is because the Union requires a consistent number of fields on both sides, preceded by the Id,title,content three fields, followed by the 1,2,3 is also three, so do not report grammatical errors, there are settings id=0 is a nonexistent record, then the result of the query is 1, 2, 3, reflected on the page, the original display ID of the place will show 1, show title of the place will show 2, show the content of the place will show 3.

As for how to continue to use, but also to see the MAGIC_QUOTES_GPC settings:

when MAGIC_QUOTES_GPC is off：

/articles.php?id=0 Union Select 1,2,load_file ('/etc/passwd ')

As a result, the contents of the/etc/passwd file are displayed where the content is originally displayed.

when MAGIC_QUOTES_GPC is on：

Using Load_file ('/etc/passwd ') directly at this point is not valid, because single quotes are escaped, but there are ways to:

/articles.php?id=0 Union Select 1,2,load_file (char (47,101,116,99,47,112,97,115,115,119,100))

The number is the ASCII of the/etc/passwd string: The string loops the output of each character ord (...)

In addition to this, you can also use the string's 16 binary: string per character loop output Dechex (Ord (...))

/articles.php?id=0 Union Select 1,2,load_file (0x2f6574632f706173737764)

Here only said the number of parameters of several attacks, belonging to the tip of the iceberg, string-type parameters, such as attack means to see the following link to the document.

Defense:

There are some software similar to SQL injection firewall on the network, such as Greensql, if the site has begun to suffer from SQL injection attacks, then use such shortcuts will often save your life, However, such software in the framework of the role of a proxy, most likely to affect the concurrent performance of the site, so in the choice of whether or not it is best to consider the objective conditions to decide carefully. Most of the time professional software is not necessary, there are many lightweight solutions, and here's how to use awk to detect possible vulnerabilities.

Create a Detect_sql_injection.awk script that reads as follows (remember not to include line numbers if you want to copy the content):

#!/bin/gawk-f
02
/\$_ (getpostcookierequest) \s*\[/{
IGNORECASE = 1
Match ($/\$.* (sqlquery)/) {
IGNORECASE = 0
Modified output ()
Next
09}
10}
11
function output ()
13 {
14 $ = $
Print "CRUD: $" \nfile: "FILENAME" \nline: "FNR" \ n "
}

This script can match a problem code similar to the following, and it is easy to extend the matching pattern as long as suit writes the IF match statement.

1: $sql = "SELECT * from users WHERE username = ' {$_post[' username ']} '";
2: $res = mysql_query ("SELECT * from users WHERE username = ' {$_post[' username ']}");

Don't forget to chmod +x Detect_sql_injection.awk before you use it, there are two ways to invoke it:

1:./detect_sql_injection.awk/path/to/php/script/file
2:find/path/to/php/script/directory-name "*.php" Xargs./detect_sql_injection.awk

will display the problematic code information as follows:

CRUD: $sql = "SELECT * from users WHERE username = ' {$_post[' username ']} '";
FILE:/path/to/file.php
Line:123

There are many ways to apply this script in the real world, such as periodically scanning program source files through cron, or automatically matching the hook method when SVN commits.

Using professional tools, testing scripts are passive defense, the root of the problem always depends on the programmer's mind is the necessary security awareness, the following are some of the guidelines must be kept in mind:

1: Numeric parameters force filtering using a method such as Intval,floatval.
2: String parameters Use a method such as mysql_real_escape_string to force filtering, rather than simple addslashes.
3: It is best to discard mysql_query such a concatenation of SQL query, as far as possible using PDO prepare binding method.
4: Using rewrite technology to hide real script and parameter information, through the rewrite is also able to filter suspicious parameters.
5: Turn off the error message and do not provide sensitive information to the attacker: Display_errors=off.
6: Log the way to record the error message: Log_errors=on and error_log=filename, regular troubleshooting, web logs are best to check.
7: Do not use the file permissions of the account (such as root) to connect to MySQL, so that shielding the load_file and other dangerous functions.
8: ...

Web site security is not complex, summed up is a sentence:filter input and escape output. Among them, we have been discussing the SQL injection problem is a filter input problem, as for the escape output problem, its representative is Cross-site scripting, but it does not belong to the scope of this article, it is not much to say.

Documents:

Addslashes () versus mysql_real_escape_string ()
SQL Injection with MySQL
Advanced SQL Injection with MySQL
Research on the content of exported fields in MySQL injection--export Webshell by injection