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=20=
=20
pg_query() and flogging someone if it is found), but is it really=20=20
necessary when the right solution is easier to use than the wrong
solution=
=20=20
? 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 =3D "SELECT * FROM ORDERS WHERE ORDER_ID =3D " +
> 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=3D'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=20=20
variables)
sql =3D "SELECT * FROM ORDERS WHERE ORDER_ID =3D " + orderId;

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

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

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

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

The little function in my previous mail is also useful for mysql which
has=
=20=20
no sup****t for parameterized queries.


--=20
Sent via pgsql-hackers mailing list (pgsql-hackers@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers