When fed the malicious image tag demonstrated above, the escapeHTML() subroutine sanitizes characters that might be misinterpreted by a web browser, causing the name to appear exactly as it was entered:

SQL Injection

A SQL injection vulnerability arises when the original SQL query can be altered to form an altogether different query. Execution of this altered query may result in information leaks or data modification. The primary means of preventing SQL injection are sanitizing and validating untrusted input and parameterizing queries.

Suppose a database contains user names and passwords used to authenticate users of the system. The user names have a string size limit of 8. The passwords have a size limit of 20.

A SQL command to authenticate a user might take the form:

SELECT * FROM Users WHERE userid='<USERID>' AND

password='<PASSWORD>'

If it returns any records, the user ID and password are valid.

However, if an attacker can substitute arbitrary strings for <USERID> and <PASSWORD>, they can perform a SQL injection by using the following string for <USERID>:

validuser' OR '1'='1

When injected into the command, the command becomes:

SELECT * FROM Users WHERE userid='validuser' OR '1'='1' AND password=<PASSWORD>

If validuser is a valid user name, this SELECT statement selects the validuser record in the table. The password is never checked because userid='validuser' is true; consequently the items after the OR are not tested. As long as the components after the OR generate a syntactically correct SQL expression, the attacker is granted the access of validuser.

Likewise, an attacker could supply a string for <PASSWORD> such as:

' OR '1'='1

This would yield the following command:

SELECT * FROM Users WHERE userid='' AND password='' OR '1'='1'

This time, the '1'='1' tautology disables both user ID and password validation, and the attacker is falsely logged in without a correct login ID or password.

Noncompliant Code Example (SQL Injection)

This noncompliant code example shows Perl DBI code to authenticate a user to a system. The program connects to a database, prompts the user for a user ID and password, and hashes the password.

Unfortunately, this code example permits a SQL injection attack because the string passed to prepare accepts unsanitized input arguments. The attack scenario outlined previously would work as described.

use DBI;
use warnings;
use strict;

my $dbfile = "users.db";
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","")
or die "Couldn't connect to database: " . DBI->errstr;

sub hash() {
# ... hash the password
}

print "Enter your id: ";
my $userid = <STDIN>;
chomp $userid;
print "Enter your password: ";
my $password = <STDIN>;
chomp $password;
my $hashed_password = hash( $password);
my $sth = $dbh->prepare("SELECT * FROM Users WHERE userid = '$userid' AND password = '$hashed_password'")
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute()
or die "Couldn't execute statement: " . $sth->errstr;

if (my @data = $sth->fetchrow_array()) {
my $username = $data[1];
my $id = $data[2];
print "Access granted to user: $username ($userid)\n";
}

if ($sth->rows == 0) {
print "Invalid username / password. Access denied\n";
}

$sth->finish;
$dbh->disconnect;

Compliant Solution (prepared statement)

Fortunately, Perl's DBI library provides an API for building SQL commands that sanitize untrusted data. The prepare() method properly escapes input strings, preventing SQL injection when used properly. This is an example of component-based sanitization.

use DBI;
use warnings;
use strict;

my $dbfile = "users.db";
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","")
or die "Couldn't connect to database: " . DBI->errstr;

sub hash() { # ... hash the password }

print "Enter your id: ";
my $userid = <STDIN>;
chomp $userid;
print "Enter your password: ";
my $password = <STDIN>;
chomp $password;
my $hashed_password = hash( $password);

my $sth = $dbh->prepare("SELECT * FROM Users WHERE userid = ? AND password = ?")
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute($userid, $hashed_password)
or die "Couldn't execute statement: " . $sth->errstr;

if (my @data = $sth->fetchrow_array()) {
my $username = $data[1];
my $id = $data[2];
print "Access granted to user: $username ($userid)\n";
}

if ($sth->rows == 0) {
print "Invalid username / password. Access denied\n";
}

$sth->finish;
$dbh->disconnect;

Use the set*() methods of the PreparedStatement class to enforce strong type checking. This mitigates the SQL injection vulnerability because the input is properly escaped by automatic entrapment within double quotes. Note that prepared statements must be used even with queries that insert data into the database.

Risk Assessment

Recommendation	Severity	Likelihood	Remediation Cost	Priority	Level
IDS33-PL	high	likely	high	P9	L2

...

[CPAN] Stosberg, Mark. CGI

[CPAN] Bunce, Tim. DBI

Vulnerability Note VU#246409: "Input validation error in quikstore.cgi allows attackers to execute commands"

...

Space shortcuts

Page tree

Versions Compared

Old Version 5

New Version 6

Key

SQL Injection

Noncompliant Code Example (SQL Injection)

Compliant Solution (prepared statement)

Risk Assessment

Space shortcuts

Page tree

Page History

Versions Compared

Old Version 5

New Version 6

Key

SQL Injection

Noncompliant Code Example (SQL Injection)

Compliant Solution (prepared statement)

Risk Assessment