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