Re: Protection from SQL injection

Hi Martijn,

> > The problem is not only quotes. The problem is all kinds of user
> > input. For example: sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " +
> > orderId; This is not a problem if orderId is a number. But what if
> > it's a String? For example "1 AND (SELECT * FROM USERS WHERE
> > NAME='admin' AND PASSWORD LIKE 'm%')". An attacker could then retrieve
> > the admin password quite quickly.
>
> In other words, your programmer was stupid. And your example doesn't
> work because no matter what the string is it can't return anything
> other than rows from the orders table. If you're worried about them
> using semicolons to introduce another query, prepare has prohibited
> that for a long time already.

The attack goes as follows: WHERE ORDER_ID = 1 yields 1 rows. WHERE
ORDER_ID = 1 AND (SELECT * FROM USERS WHERE NAME='admin' AND PASSWORD
LIKE 'a%') yields 0 rows. OK that means that the admin password
doesn't start with an 'a'. If WHERE ORDER_ID = 1 AND (SELECT * FROM
USERS WHERE NAME='admin' AND PASSWORD LIKE 'b%') yields 1 row we know
the admin password starts with 'b'. For an average password length of
6 it takes 6 * 64 queries to get the password, plus some to get the
user name, plus maybe a few to get the table name and column name
correct.

> But as far as I'm concerned, the real killer is that it would make
> using any interactive query interface impossible.

No. Literals is an access right, and the interactive query tool may
have that access right. Let's say we have a APP_ROLE (for the
application itself) and a QUERY_ROLE. The default is literals are
enabled, that means the query tool can use literals. For the
application, the administrator may chooses to revoke the right to use
text and number literals using REVOKE LITERAL_TEXT, LITERAL_NUMBER
FROM APP_ROLE. Or the developer himself may want to try out if his
application is safe, and temporarily disables LITERAL_TEXT first. He
then runs the test cases and fixes the problems. Afterwards, he may
disable even LITERAL_NUMBER and try again. For production, maybe
literals are enabled.

Regards,
Thomas