On Tue, 2011-06-28 at 16:19 -0400, Johannes B. Ullrich wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
> couple links to articles I wrote about some of the issues.
Thanks for the links :)
> Probably the #1 lesson: Prepare your SQL statements properly.
Yes, and there is another method that I use beyond prepared statements.
It also helps if you start using pretty URLs. Which can hide underlying
request parameters and such being passed on for other things to process.
Like parameters used as part of a SQL statement. Might see about doing a
presentation on URL rewriting for pretty URLs.
Back to my other method, any time I am passing string values as part of
an SQL statement, I will look for and replace all single quotes with two
single quotes. If I am expecting a numeric value, a simple test for is
it a number or stripping all but numeric characters is suffice. Both can
be done for values passed prepared statements as well.
I started doing single quote replacement with two in order to prevent
single quotation marks from breaking strings. Since in Firebird and most
RDBMS a string is represented by two single quotes, not double quotes
like in most languages. Single quotes are used for characters, not
strings in most languages, but database differ for some odd reason.
That simple replace all single quotes with two single quotes, to not
break strings. Ends up being almost as good as a prepared statement. Any
time someone tries to inject SQL it just ends up as part of the string
value. While they are trying to end that statement and start another. Or
they are trying to inject a statement as a sub query. There is simply no
way around the quoting replacement or way to break the quotes and start
a new statement.
For example
value='some string value'
SELECT * FROM TABLE WHERE COL=value
Now if there was a single quote in the string it would break the quotes.
Thus any single quotes have to be treated differently. If I passed just
some string's value, it would break the quotes. So when ever I pass a
value I do a replace all single quotes with double quotes
value='some string's value' <- breaks sql statement
In Java (and languages with replaceAll)
value.replaceAll("'","''");
In JavaScript (lacking replace all thus using regex)
value.replace(/'/g,"''")
Result
value='some string''s value' <- does not break, and can't break
You can substitute what ever you want in the value being passed directly
into the query, even SQL. It will all be treated as a string and there
is no way to break the string or get around it. Even if you try to pass
in single quotes, any one quote becomes two. Just end up with lots of
quotes and still no string breakage. It essentially provides similar
functionality as prepared statements, with regard to preventing unwanted
SQL statements.
For this to work, it MUST be done server side before the SQL statement
is executed. The JavaScript example is just to show another method if a
language lacks replaceAll function/method. If you use JavaScript to
correct your SQL before its executed. You have some serious problems,
and need to change that. Since anyone can tweak the client side
JavaScript at any time.
Also this just works for strings. Numbers must be treated differently,
and in some cases you can quote numbers. But some database do not like
seeing strings when they are expecting a numeric values, others don't
care. But you have other options with numbers as previously mentioned.
--
William L. Thomson Jr.
Obsidian-Studios, Inc.
http://www.obsidian-studios.com
---------------------------------------------------------------------
Archive http://marc.info/?l=jaxlug-list&r=1&w=2
RSS Feed http://www.mail-archive.com/[email protected]/maillist.xml
Unsubscribe [email protected]
