Copyright Notice
This text is copyright by CMP Media, LLC, and is used with their permission. Further distribution or use is not permitted.This text has appeared in an edited form in SysAdmin/PerformanceComputing/UnixReview magazine. However, the version you are reading here is as the author originally submitted the article for publication, not after their editors applied their creativity.
Please read all the information in the table of contents before using this article.
Unix Review Column 58 (may 2005)
[suggested title: ``Avoiding SQL Injection Attacks'']
In recent months, the entertainment news was abuzz with how socialite Paris Hilton had had her Sidekick phone ``hacked'', revealing phone numbers for many famous people, interesting notes about possible profit participation in the ``stolen'' sex tape, and lurid pictures, all of which were broadcast widely on the Internet.
But the quiet word amongst computer security professionals was that this wasn't a matter of hacking into her phone at all, not that the entertainment news people would know or care. Instead, certain individuals have claimed responsibility through a well-known security flaw on T-Mobile's web site, with which the phone is synchronized. Thus, it wasn't the phone as the source of information: it was the website.
While the precise steps by which the intruder obtained access may never be known, one of the most common intrusion paths is the SQL Injection Attack. An intruder either guesses by trial and error, or by examining the source code of the application, that some field in a form is being used to construct an SQL query. Unless the application carefully verifies the content of this field, or quotes the unusual characters properly, it might be possible for the field's value to escape beyond its intended use, and create additional SQL constructs.
Let's look at a specific example. Suppose a bank web application is trying to show all the bank account balances that belong to me. The SQL might look like:
SELECT account_id, balance FROM account_data WHERE account_owner = '[ME]';
Here, the [ME] comes from some other part of the calculation, which isn't relevant. The effect is that I see only my data. Now, let's say that the web programmers at the bank upgrade the application so that I can distinguish my personal and business accounts via a pop-up form element, which returns back a ``1'' for personal and ``2'' for business. So, they change this SQL to be:
SELECT account_id, balance FROM account_data WHERE account_owner = '[YOU]' AND account_type = $account_type;
Of course, they presume that the values ``1'' and ``2'' will drop in quite nicely there, and the application tests out just fine, so the programmers move on to the next item in their long to-do list.
What many web programmers fail to realize is that just because a pop-up form specifies a list of values or a textfield provides a maximum length (even enforced by javascript), this doesn't mean that a user can't construct an arbitrary value for any returned form parameter. Of course, changing this value to ``3'' won't do me much good. I won't get anything at all, since there aren't any accounts with a type of 3.
But suppose I jimmy up a return value so that $account_type
comes back
as: 1 OR 99 = 99
. Let's fill that in:
SELECT account_id, balance FROM account_data WHERE account_owner = '[YOU]' AND account_type = 1 OR 99 = 99;
Oops! What just happened? Since the OR
has a lower precedence
than the AND
, we're saying essentially ``true'' for every row of the
table. Presuming we have the patience to page through the result, we
now have everyone's account numbers and balances. Ooops.
There are many other variants on SQL injection attacks. You might be rather shocked when you begin a web search for further information.
But let's cut right to right way to do this, with DBI placeholders. The DBI interface permits a chunk of SQL to contain question marks wherever data is being used. For example, the SQL above could be written as:
my $sth = $dbh->prepare(q{ SELECT account_id, balance FROM account_data WHERE account_owner = ? AND account_type = ? });
This takes an existing database handle (here, $dbh
), and creates a
statement handle ($sth
) that is safely protected from SQL injection
attacks. We use this handle by calling execute
, providing the
values to be inserted:
$sth->execute('ABC 123', 2);
The DBI system goes through the data, figuring out how to quote the values properly for the corresponding database. For example, the statement being executed will likely end up being something like:
SELECT account_id, balance FROM account_data WHERE account_owner = 'ABC 123' AND account_type = 2
Note that the string was enclosed in single quotes, but the number remained unquoted. (This is important for databases that respect data types.) Now, let's see what happens if we try to pass that complex string as the account type number:
$sth->execute('ABC 123', '1 OR 99 = 99');
This results in the effective SQL of:
SELECT account_id, balance FROM account_data WHERE account_owner = 'ABC 123' AND account_type = '1 OR 99 = 99'
Ahh, look! The quotes enclose the now-apparent string value. While the query is likely to fail (no account type will match that), we didn't get to see everyone's account balances either. Success.
The execute
method should get one value for every placeholder in
the query string. The undef
value is properly mapped as NULL
,
while numbers and strings pass through properly quoted for the active
database. Certain types may require hinting as to the actual data
type: see the DBI
documentation for further information.
One common problem when using placeholders is that we must maintain a
correlation between chunks of SQL and the actual values we want to
plug in. Good discipline helps here, but there's a nice module in the
CPAN called SQL::Abstract
that helps us say what we intend with a
bit more clarity.
One feature of SQL::Abstract
is that a particular WHERE
clause
can be represented by relatively straightforward complex data
structure. For example, AND
-ing two conditions is represented
by a hashref:
{ account_owner => $owner, account_type => $account_type }
Here, $owner
contains our <ABC 123> and $account_type
is 1 or 2.
Using SQL::Abstract
, we can convert this to the SQL to feed to
prepare, and the bind parameters to feed to execute
with one simple
step:
use SQL::Abstract; my $sqa = SQL::Abstract->new;
my ($owner, $account_type) = ('ABC 123', 2); # from inputs
my ($sql, @bind) = $sqa->select( 'account_data', # table [qw(account_id balance)], # fields { account_owner => $owner, account_type => $account_type }, # "where" );
my $sth = $dbh->prepare_cached($sql); # reuse SQL if we can $sth->execute(@bind); # execute it for this query
The result is a good query, with good bind values in the right order.
And we're still safe from SQL injection attacks. If we also wanted to
add account_status
as not in suspended or deleted, we could change
the query to:
{ account_owner => $owner, account_type => $account_type, account_status => { -not_in => ['suspended', 'deleted'] }, }
In this case, the corresponding SQL looks like:
SELECT account_id, balance FROM account_data WHERE ( account_owner = ? AND account_status NOT IN ( ?, ? ) AND account_type = ? )
with bind values of:
'ABC 123', 'suspended', 'deleted', 2
Note that the ordering of the hash elements affects the output, but the bind values did indeed line up with the corresponding placeholders.
I won't repeat the entire manpage for SQL::Abstract
here, but it
looks sufficient for most of the queries I've ever wanted to create in
SQL.
I can even use SQL::Abstract
with my Class::DBI
-wrapped
databases using Class::DBI::AbstractSearch
. Mixing in this class
to my Class::DBI
-derived base class gives me the search_where
clause, accepting that same data structure. For example:
my @results = My::DB::AccountData->search_where({ account_owner => $owner, account_type => $account_type, account_status => { -not_in => ['suspended', 'deleted'] }, });
And there, no SQL to write at all, again, and I'm safe from SQL Injection Attacks.
I hope you find SQL::Abstract
useful, and never have your name
appear in a headline regarding a bad security breakin. Until next
time, enjoy!