What is proper defence against SQL injection?
From the point of view of defence, there are two things to do:
- Find all SQL injection vulnerabilities
- Patch these vulnerabilities
To solve these two problems, we can effectively defend against SQL injection attacks.
SQL injection defense is not a simple matter; developers often run into some errors. For example, only doing some escape processing for user input is not enough. Refer to the following code:
$sql = "SELECT id,name,mail,cv,blog,twitter FROM register WHERE id=".mysql_real_escape_string($_GET['id']);
When the attacker constructs some injected code like this:
http://vuln.example.com/user.php?id=12,AND,1=0,union,select,1,concat (user,0x3a,password),3,4,5,6,from,mysql.user,where,user=substring_ index(current_user(),char(64),1)
it will successfully bypass mysql_real_escape_string and complete the injection.
Because mysql_real_escape_string() is escaped,
- \ r
- \ n
these characters are not used in this payload of the SQL injection.
Will that be ok after increasing some filtering characters or some special characters, such as those dealing with “space” and “brackets,” including some SQL reserved words like SELECT and INSERT.
In fact, such a blacklist-based approach will more or less cause some problems. Here is an example that does not require the use of a space in injection:
This is an example with no use of brackets and quotation marks in which 0x61646D696E is the hexadecimal encoding of the string admin:
SELECT passwd from users where user=0x61646D696E
In SQL, reserved words like “HAVING” and “ORDER BY” may all occur in natural language, and the normal data submitted by the user may also include these words, resulting in wrong debugging, and cannot easily be filtered.