| Products |
| Web How-To |
| Free Tools |
| FAQ |
| Contact Us |
| Our Clients |
You are here: Home > Articles > SQL Injection
SQL Injection
SQL Injection is a widespread but little known problem amongst software developers and web masters. If you use an SQL based database such as MySQL, Oracle or Microsoft SQL Server you may be at rist of an SQL Injection attack from any of your web-based applications that use a database.
What is SQL Injection
Any user-supplied or hidden form field in a web application, or any user supplied field in any application that connects to a database is at risk. Users can enter data that the SQL processor believes are commands.
Why is SQL Injection a Problem?
By default, fields a user enters are not validity checked for this type of exploit, the programmer must specifically ensure that special characters are handled properly. This is where programmer-error may leave some more all user-entered, hidden fields, or tracking cookies at risk from a malicious user.
What could a malicious user do?
From mybassing authentication, or collecting information about other users in your database, a malicious user could even insert a "drop table" command and delete all of data and cripple your applications. For database user accounts that have administrator priveledges, a malicious user could create accounts, change or collect passwords or even execute shell commands to compromise your server.
But I do input validation in Javascript!
Even if you check for user input issues with Javascript, you must always double check the fields and hidden values or cookie values before using them in an SQL statement. A malicious user could just save your form on their local computer and edit the HTML (or create their own) to craft an SQL Injection attack against your web application.
How can I tell is my application is at risk of SQL Injection?
The table below contains some of the most common SQL Injection attacks.
| Attack String | As URL Encoded | Attack |
|---|---|---|
| ' | %27 | This is the quickest test for SQL Injection, if an SQL error or unhandled application error is created from adding an appostrophy, your application is at high risk. |
| ';-- ;-- | %27%3b%2d%2d %3b%2d%2d | SQL Comment. This command is used to make the rest of a line of SQL code a comment, very useful to insert your own command and truncate the rest of the line. |
| % %% | %25 %25%25 | SQL Wildcard. Useful for non-escaped text fields such as numbers to match all records when used in a where statement. |
| 'OR''=' | %27%20OR%27%27%3d%27 | True statement, for when a user field is used in a where statement between signle quotes. |
| +OR+1=1 +OR+1%3d1 | %20OR%201=%20 %20OR%201%3d%20 | True statement, for when a user field is used in a where statement not within quotes, such as integer values. |
How does the attack work?
Here's a typical SQL command:
SELECT * from users where username='$username';SELECT * from users where username='' or 1=1 or username = '';SELECT * from users where username=''; drop table users; select * from billing where account <> '';
Preventing SQL Injection Attacks
Escape all variables before adding them to an SQL statement. PERL has an included function called DBI::quote which can be used:
my $dbh = DBI->connect("dbi:$db_type:$db_schema:$db_host", "$db_user", "$db_password") or die "DB Error!\n";
my $username = $dbh->quote($username);
my $q1 = $dbh->prepare("select * from users where username = $query");
my $rows = $q1->execute || die "DB Error!\n";
$q1->finish();
$dbh->disconnect();
This does get a bit complicated for handling very complicated inserts etc, this function will handle most SQL Injection attacks as long as you put all your data in 'quotes':
sub mysql_escape {
my $string = shift;
$string =~ s/(\'|\"|--)/\\$1/g;
return $string;
}
sub mysql_unescape {
my $string = shift;
$string =~ s/(\\')/\'/g;
$string =~ s/(\\")/\"/g;
return $string;
}
Additionally, it's a good idea to limit the scope of the user that you use in your web application to limit damage to your database. It's also a good idea to have all fields within quotes 'value', with all the various character sets emerging, it's much easier to exploit a field not within quotes.
Free Security Evaluation of Your Web Apps
Contact us today for a free no obligation review of your web application. While Stellar will not conduct penetration testing or an active evaluation of any website or application, we can review the concept or source code to provide an evaluation of problem areas. Stellar's web application subject matter experts can help plan and implement changes to prevent misuse and hacking of your web application.