|
Note the usage of one way MD5 encryption to ensure that a
user’s password is kept secret at all times. Using this
SQL query a user may enter their username as ‘pappu’
and their password as ‘secret’ and the query executed
to check this authentication would be:
SELECT user id FROM users WHERE username=’pappu’
AND password=MD5(’secret’)
Which would correctly identify the user as this is the correct
usage.
However, a malicious user would be able to exploit the lack
of SQL injection detection by entering a carefully crafted
piece of text into the password input box, for example “’)
OR user id<>(’”. At first glance this does
not seem particularly damaging, but without being noticed
the following SQL query would be executed:
SELECT user id FROM users WHERE username=’pappu’
AND password=MD5(’’) OR user id <> (’’)
Which instead of identifying a user based on the username
and password provided would simply return the user ID of the
first tuple in the table ‘users’, this would clearly
be disastrous for any system.
There are two common methods of avoiding SQL injection vulnerabilities
being exploited within systems, they are detection of the
exploit being attempted and escaping of all user input prior
to using it within any SQL query, this system uses the latter
although I will explain the fundamental points of both.
Detection
It is possible to detect an attempted SQL injection attack
by checking all user input via regular expressions for SQL
meta-characters. SQL meta-characters are used within attacks
to either invalidate the current SQL query and to execute
a second query or to add parameters to the query, the characters
are a single quote (’) or a double-dash (--). If any
of these characters are detected within the user input then
appropriate steps can be taken to ensure that the attack does
not succeed. The example above would not have succeeded if
detection was incorporated as the single-quote within the
password input field would have been detected.
Escaping
SQL escaping is a similar method of prevention as
HTML escaping in that all user data is made safe by escaping
it. SQL meta-characters such as a single-quote (’) or
a double-quote (”) can be escaped using a back slash
and would no longer affect the SQL query’s functionality.
Using the above example, if escaping was being used then when
the attack was issued then instead of the user authentication
system being effectively bypassed, the following SQL query
would be executed:
SELECT user id FROM users WHERE username=’pappu’
AND password=MD5(’\’) OR user id <> (\’’)
Which while looking very similar to the vulnerable query
is actually very different. The MD5 function is now being
passed the full string “\’) OR user id <>
(\’” as it’s parameter and the query returns
no tuples as it should.
|