[SQL Injection] at tagSwap.net: Open Source Lasso Code

[SQL Injection]

Bil Corry, December 01, 2004

SQL injection is the security concern where an improperly formed sql inline may allow someone to "inject" their own SQL queries into your sql inline; by doing so, they may be able to insert, modify, retrieve or delete data.

An improperly-formed sql inline is one where raw user-provided data is directly plugged into a sql inline without validation. This includes hidden inputs in forms! The rule-of-thumb is all data coming from the user must be treated as hostile until validated.

For this article, we're going to examine the five most common injection points in sql inlines and how to secure them:

strings

numeric values

date values

LIKE queries

REGEXP queries

Strings

SQL injection into strings happens when a user-provided string is merged into a sql inline and the quotes used to delimit the SQL string are also contained within the user-provided string.

Did you get that? Let's look at an example, it'll become more clear.

Here is a sql query that plugs in the user-provided value for searching on a first name:

var:'sql'="
 
 
 
 SELECT 	
	
name_first, 	
	
name_last
 
 
 
 FROM 	
	
contacts
 
 
 
 WHERE 	
	
name_first='"+(action_param:'name_first')+"'";

Imagine name_first = "Bil" - the sql query becomes this:

SELECT 	
	
name_first,  	
	
name_last 	
FROM 	
	
contacts 	
WHERE 	
	
name_first='Bil'

So far, so good, right? Now imagine instead the user entered:

crackerboy';
 delete from contacts;
 #

Now the query becomes:

SELECT 	
	
name_first, 	
	
name_last
FROM 	
	
contacts
WHERE 	
	
name_first='crackerboy';
 delete from contacts;
 #'

Viola! Your entire contacts table is hosed. (The pound sign # tells MySQL that the rest of the line is a comment).

So how do you stop 'crackerboy' from sql injection in strings? You need to escape all the quotes. Fortunately, Lasso has a built-in tag to do it for you, [encode_sql]

Here's our original query rewritten to use it:

var:'sql'="
 
 
 
 SELECT 	
	
	
name_first, 	
	
	
name_last
 
 
 
 FROM 	
	
	
contacts
 
 
 
 WHERE 	
	
	
name_first='"+(encode_sql:(action_param:'name_first'))+"'";

So properly encoded, now the query becomes:

SELECT 	
	
name_first, 	
	
name_last
FROM 	
	
contacts
WHERE 	
	
name_first='crackerboy\';
 delete from contacts;
 #'

That slash \ in front of the embedded quote tells MySQL to ignore it, and thus, the entire string becomes the name_first search criteria.

So that's how you protect your strings in queries, but what about numeric values?

Numeric Values

Unfortunately, numeric values can not be protected using encode_sql, reason being you wouldn't (normally) have quotes around the value to begin with, so you wouldn't need to escape any quotes being sent.

But I'm getting ahead of myself. Let's take a look at a query that uses a numeric value:

var:'sql'="
 
 
 
 SELECT 	
	
name_first, 	
	
name_last
 
 
 
 FROM 	
	
contacts
 
 
 
 WHERE 	
	
userid = "+(action_param:'userid');

Imagine userid = "123" - the sql query becomes this:

SELECT 	
	
name_first,  	
	
name_last 	
FROM 	
	
contacts 	
WHERE 	
	
userid = 123

Already this has a problem, the user could feed sequential numbers to your query and pull out the entire database list. But that's another topic for another time.

Now imagine instead the user entered:

123;
 delete from contacts;

Now the query becomes:

SELECT 	
	
name_first,  	
	
name_last
FROM 	
	
contacts
WHERE 	
	
userid = 123;
 delete from contacts;

Viola! Your entire contacts table is hosed. (Notice the theme here?)

And even if we encode_sql the passed-in value, it wouldn't change anything.

So how do you prevent crackerboy from hosing your table this time? Make sure that you explicitly pass a numeric if that's what your query is expecting.

While you could validate the passed-in value to ensure it's numeric using something like [string_isdigit], I instead just cast it explicitly to a numeric using [integer] and [decimal] (depending on if the query needs an integer or decimal).

So let's rewrite that query to prevent sql injection for integer:

var:'sql'="
 
 
 
 SELECT 	
	
name_first, 	
	
name_last
 
 
 
 FROM 	
	
contacts
 
 
 
 WHERE 	
	
userid = "+(integer: (action_param:'userid'));

So what happens, you ask, if the user provides the following?

123;
 delete from contacts;

Well, when Lasso converts a string to an integer or decimal, it takes every digit it can until it hits a non-numeric character. So casting to integer, the query would become:

SELECT 	
	
name_first,  	
	
name_last 	
FROM 	
	
contacts 	
WHERE 	
	
userid = 123

Of course, decimals are handled in an identical fashion. Make that your homework assignment.

Date Values

Dates in MySQL can either be strings or numeric, MySQL accepts either. But to prevent SQL injection, the trick is to cast the passed-in value to a Lasso date type using [date] and formating the output for MySQL. I won't go into how you convert a string to a date type as it depends on your implementation, but I will show you how to create a sql query using a Lasso date type:

var:'sql'="
 
 
 
 SELECT 	
	
name_first, 	
	
name_last
 
 
 
 FROM 	
	
contacts
 
 
 
 WHERE 	
	
last_login <= "+($LassoDate->(format:'%Q'));

Since a Lasso date type can not hold anything other than dates, you are assured that any bogus strings will be rejected when trying to cast it to a Lasso date.

LIKE queries

LIKE queries are really just strings, but with an extra twist of pattern matching. What that means is LIKE queries will recognize special characters within the string as wildcard charaters, namely, % and _. You can read more about what % and _ do in a pattern-matching query on the MySQL website.

To show how % and _ can be abused, let's start off with an example query:

var:'sql'="
 
 
 
 SELECT 	
	
name_first, 	
	
name_last
 
 
 
 FROM 	
	
contacts
 
 
 
 WHERE 	
	
name_last LIKE '"+(encode_sql:(action_param:'name_last'))+"%'";

This query will allow someone to enter the first letter(s) of a last name, and find all contacts that begin with them.

So imagine the user entered "cor" (you require and validate that they enter at least three letters), the query would look like:

SELECT 	
	
name_first, 	
	
name_last 	
FROM 	
	
contacts  	
WHERE 	
	
name_last LIKE 'cor%'

But imagine if the user entered "%%%", the query would become:

SELECT 	
	
name_first, 	
	
name_last 	
FROM 	
	
contacts  	
WHERE 	
	
name_last LIKE '%%%%'

which would find all of your contacts! So how to handle LIKE? You have two options. Either delete all % and _ found in the string or escape them by replacing % with \% and _ with \_. Be sure to still encode_sql the string. Note that you only escape % and _ for LIKE queries (or any of the other pattern-matching queries that recognize % and _), don't go doing it on all your query strings.

REGEXP queries

REGEXP queries is another pattern matching query that has a whole slew of special characters. Unless you want your users passing their own regular expressions, most likely you'll just want to encode their value to be used in a regular expression you designed. The trick is to precede every character in the string with double backslashes \\, so a passed-value of "1+2" becomes "\\1\\+\\2".

(Side note: You have to do the same thing for Lasso's regexp tags if you're feeding them user data)

Bil Corry provides Lasso development and consulting services.

[SQL Injection]

Strings

Numeric Values

Date Values

LIKE queries

REGEXP queries

Articles