Re: Protection from SQL injection

>> zero developer pain
>
> Actually it's not zero pain, but the main problem is: there is no way
> to enforce using it.
Sure, there is no way to enforce it (apart from grepping the source for
pg_query() and flogging someone if it is found), but is it really
necessary when the right solution is easier to use than the wrong solution
? Capitalizing on developer laziness is a win IMHO, lol.

> 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.
IMHO this is an example of what should never be done.

// very bad (especially in PHP where you never know the type of your
variables)
sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " + orderId;

// slightly better (and safe)
sql = "SELECT * FROM ORDERS WHERE ORDER_ID = " + int( orderId );

// correct (PHP syntax)
pg_query_params( "SELECT * FROM ORDERS WHERE ORDER_ID = $1",
array( orderId ))
db_query( "SELECT * FROM ORDERS WHERE ORDER_ID = %s", array( orderId ))

// correct (Python syntax)
cursor.execute( "SELECT * FROM ORDERS WHERE ORDER_ID = %s", ( orderId, ))

The last two don't complain if orderId is a string, it will be correctly
quoted, and then postgres will complain only if it is a string which does
not contain a number. This is useful in PHP where you never know what type
you actually have.

The little function in my previous mail is also useful for mysql which has
no support for parameterized queries.