Preventing SQL-smuggling in ACS

by Branimir Dolicki

ACS Documentation : ACS Core Architecture Guide : Preventing SQL-smuggling in ACS

The Problem

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
Or:
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.

Conclusion

This solution:
carsten@arsdigita.com, branimir@arsdigita.com