Lasso Soft Inc. > Home

  • Articles

Understanding and Overcoming SQL Injection

Lasso's relative ease of use can lead to a level of complacency unless we understand how to secure our code from unwelcome attention. Even a house with a great security system is not secure if you leave a window open. One area that warrants special mention for developers is SQL Injection.

What is SQL Injection?

Simply put, SQL injection is a technique hackers use to execute malicious SQL code by using inputs or GET parameters.

local(id = action_param(‘id’))
inline(..., -SQL=’SELECT * FROM table WHERE id = ‘+#id)
	found_count
/inline

Usually the above code would be using an integer id in the select, but it is possible to pass a string in the GET or POST param to compromise security.

Using the URL "/mypage.lasso?id=1;DROP table LOG;" it would be possible to force the code above to drop a table!

How do I guard against it?

The golden rules are these:

  • Never ever trust user input on any given field to be what it is supposed to be;
  • Always specify the types of your variables;
  • Always encode_sql incoming strings in your SQL statement; and
  • Only provide permissions to the MySQL db user specified in Lasso Security that you need to.

Dealing with Integers

The key problem in the example code above is that #id is not forced to an integer data type.

There are two ways you can protect in this specific case:

1. specify #id as an integer when initially set, and whenever there is a "foreign" element incoming to it.

// this leaves the gate wide open
local(id = action_param('id'))

// this forces an integer and protects from SQL injection.
local(id = integer(action_param('id')))

2. be loose with the value initially but specify as an integer when used in a SQL statement.

local(id = action_param('id'))
inline(..., -SQL='SELECT * FROM table WHERE id = '+integer(#id))
	found_count
/inline

Or using a standard Lasso Inline:

local(id = action_param('id'))
inline(..., -table=”table”,”id” =integer(#id),-search)
	found_count
/inline

Protecting string input

It gets a little easier when dealing with strings, because we have "encode_sql" to help out.

local(aname = action_param('aname'))
local(sql = 'SELECT * FROM table WHERE aname = "'+encode_sql(#aname)+'"') // *

inline(..., -table=”table”,”aname” =encode_sql(#aname),-search)
	found_count
/inline

* In the constructed SQL statement, note the quote order: a double quote followed by a single quote to open, then a single quote followed by a double quote to finish.

This means that the incoming string will be scrubbed for nasties - certain characters are escaped so that the string, even if it contains fragments of SQL will not be executed but instead will be treated as a bonafide string to MySQL.

Just a note on *when* to encode_sql the strings: do it at the time you are using the variables in your statement so that you *know* it is done, and also if you have to do any string operations on the var beforehand you are performing the ops on a modified string which may yield undesired results.

Other data types

The same rule applies to data types other than strings and integers: specify them as the data type you intend them to be.

If it is supposed to be a decimal, specify it as such. If in doubt, specify it again.

Dealing with user input

Always use validation on any user input to make sure what is entered meets your data criteria. Make sure dates are dates, email addresses are email addresses, and that field lengths for all entries that don’t require narrative are as short as they need to be for their function. Server side validation will always yield more concrete results than client-side validation, but usability issues and practicality can also dictate your validation method(s) of choice.

Lock down MySQL user permissions

It is too easy to use defaults: don’t be tempted. The extra effort in specifying your own user access configurations is a low price to pay for more peace of mind for you and your clients.

If your Lasso user does not need access to every database on the host, don’t give it access. I go so far as to say it’s unwise to regardless. Giving the user access to all databases on the host also opens up access to the “mysql” db and provides a potential attacker a vector with which to open up permissions to malicious sources.

If your Lasso user does not need “DROP” or “GRANT” permissions, don’t set them!

See: http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html for more information

References

MySQL Security Privileges http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html

Author: Jonathan Guthrie
Created: 12 Aug 2011
Last Modified: 12 Aug 2011

Comments

Encode_sql does not protect against use in LIKE queries

by Jolle Carlestam, 15 January 2012

Excellent article.
There is one threat that's not covered and won't be dealt with using encode_sql. Bil Corry brought this up on an LDC presentation in Chicago 2008: All Your Base Are Belong To Us.

When using user input in LIKE statements (or any of the other pattern- matching queries that recognize “%” and “_”) it's important to escape these chars. Failing to do so will open up for exploits where a user can get access to the entire table and not just the record you wanted to provide.
There is a Lasso 9 method on tagswap that will help you protect your tables against this kind of attack.
http://tagswap.net/encode_sqlfull

Note that it's only useful when actually using LIKE (and similar) statements. In all other string cases Lassos built in encode_sql is sufficient.

Here's an example where it's needed;
var(sql = 'UPDATE mytable SET myfield = "' + encode_sql($myvalue) + '"
WHERE otherfield LIKE "%' + encode_sqlfull($myvalue) + '";')

In the first instance, where we set a field to a string value, using encode_sql is the right choice. After all, if the string contains % we would not want that stored escaped in the field.
In the second instance, the WHERE section, we need to use encode_sqlfull so that we prevent malicious use of % chars in the search criteria.

BTW, the Knop framework for Lasso 9 has this protection built in.

You must be logged in to comment.

Please note that periodically LassoSoft will go through the notes and may incorporate information from them into the documentation. Any submission here gives LassoSoft a non-exclusive license and will be made available in various formats to the Lasso community.

LassoSoft Inc. > Home

 

 

©LassoSoft Inc 2015 | Web Development by Treefrog Inc | PrivacyLegal terms and Shipping | Contact LassoSoft