Preventing SQL-smuggling in ACS
by Branimir Dolicki
ACS Documentation :
ACS Core Architecture Guide :
Preventing SQL-smuggling in ACS
You construct your SQL on the fly in your Tcl script. The SQL is
parametrized by user input comming from form variables. If you don't check for user
input a cracker can smuggle additional SQL and gain access to your system. Typically
your WHERE clause looks like this
... WHERE user_id=$user_id
where user_id is collected from an URL variable.
If you don't check the content of user_id before constructing your SQL a
cracker can add additional logical clauses and even complete subqueries or UNION
clauses thus obtaining pretty much anything from your database.
The One Month Solution
Check every Tcl script in your server and make sure every form variable
is checked either in the script itself or outside by an external filter.
Educate your programmers so that they always write code that doesn't
suffer from this problem. Create a code-review process to make sure
that no script ever goes live without being checked against security.
Enforce usage of a secure database API...
The Five Minutes Solution
Install Carsten's global filter
that will check every URL paramateter. If the parameter looks like a
part of a SQL WHERE clause it will reject the request.
How does it work
For each form variable that came with the request ask Oracle whether
it would accept the following SQL statement:
select 1 from dual where 1 = $value
select 1 from dual where 1 = '$value'
The first form guards against smuggling SQL where integers are expected
while the second form does the same for character strings.
So you go to Oracle with every form variable for request???
No. We first do some regexp checks which make further processing unecessary for vast
majority of cases. We go to Oracle only if one of these regexps maches:
Regexps are generally cheap and the majority of pages in ACS do things
that are much more expensive than these two regexps (such as database calls).
Still, you are going to Oracle with every form value that contains word "OR"!
Yes. But how many normal hits contain form values with word OR in
them? (A word, not just the two letters - see the regexp above - words
such as "word" don't match). Actually the only form values
that might contain the word OR are from forms where users actually
submit contributions. Those normally consist a tiny fraction of all
hits that the site receives.
Doesn't this filter sometimes reject legitimate content?
Theoretically that is possible. But in practice
it is almost impossible to accidentally write a string that will become a
valid WHERE statement after "1 = " is prepended to it.
Even in a forum for SQL nerds or on pages that actually accept SQL as
input parameters - it will NOT lead to a valid SQL statement.
Besides, Carsten's program writes an Error entry to your log file whenever
a request is rejected and you'll be informed about it by the Watchdog
so if it ever happens that a legitimate request gets rejected you'll
know about it (and you'll be able to appologize to the user personally,
for example). But I repeat: that is _very_unlikely_.
Are there SQL-smuggling attacks this filter cannot prevent
There is only one we are aware of. If you collect data from URL
path and not from form variables. For example Wimpy Point uses URLs
like /wp/display/581/. That means that you have to grep for
register_proc among your initialization scripts and see whether
the registered procs check user input. In ACS we do it only in a handful
of places but if you have custom code you should check.
- Guards against all known SQL-smuggling attacks where hostile
SQL is passed in form variables
- Does not requre editing or even checking any of your scripts
- Makes your site more secure in five minutes than checking every single
file would make it in one month
- Does not significantly increase your server load
- Is not likely to reject legitimate requests