What is a parameter marker?
In the realm of applications that access a relational database through some sort of API, parameter markers can be used to indicate where the values of application values are to be positioned in an SQL statement. This is essentially a standard feature of most major database development environments. To clarify the term "database development environment", I am referring to the specific database system (i.e. DB2, mySQL, Oracle, MS SQL Server, Postgres...etc), the API used to access it, and the appropriate database driver if using a database-independent API like Perl's DBI module, Java's JDBC, or ODBC. You should consult your database driver's documentation (or native API if you're using that) to ensure that parameter markers can be used. In this article, you'll see how this works for creating secure database applications.
For the examples in this article, I will be using Perl's DBI module. The code snippets here are not complete and are meant for demonstration purposes only.
A Simple Example: Fending off an SQL Injection Attack
The Problem:
use DBI;
use strict;
# Connect to the database, returning a database handle
my $dbh = DBI->connect($data_source, $username, $password)
or die $DBI::errstr;
# Form our SQL statement.
my $sql = "select name, description, price from products where category = '$somevalue'";
# Prepare the SQL statement, returning a statement handle
my $sth = $dbh->prepare($sql) or die ;
# Execute
my $sth->execute() or die $DBI::errstr;
# Fetch the rows and do something with them
while (my ($name, $description, $price) = $sth->fetchrow_array ) {
# do some stuff....
}
# Clean up and disconnect from the database.
$sth->finish();
$dbh->disconnect();
my $sql = "select name, description, price from products where category = '$somevalue'";, especially if $somevalue is determined by user input. The problem here is that $somevalue can be anything, and no checks are done to ensure that proper quoting can be done with ths pair of single quotes around the literal value $somevalue. If the user were to enter a string with a single quote as part of $somevalue, the query will most likely fail with a syntax error in the SQL.
select name, description, price from products where category = 'food' or 'a'='a'
select name, description, price from products where category = 'food';delete from users where 'a'='a'
$username has only the database authority absolutely required.)
use DBI;
use strict;
# Connect to the database, returning a database handle
my $dbh = DBI->connect($data_source, $username, $password)
or die $DBI::errstr;
# Form our SQL statement.
my $sql = "select name, description, price from products where category = ?";
# Prepare the SQL, returning a statement handle
my $sth = $dbh->prepare($sql) or die ;
# Execute
my $sth->execute($somevalue) or die $DBI::errstr;
# Fetch the rows and do something with them
while (my ($name, $description, $price) = $sth->fetchrow_array ) {
# do some stuff....
}
# Clean up and disconnect from the database.
$sth->finish();
$dbh->disconnect();
$somevalue to the execute function. This implicitly binds the value of $somevalue to the marker, and will properly quote and escape the contents. So, if someone tries to submit food' or 'a'='a, we'd get:
select name, description, price from products where category = 'food' or 'a'='a'