jerbear jerbear - 2 months ago 9
PHP Question

PHP and PostgreSQL: Avoiding cross-site scripting and SQL injection attacks

When inserting data into a PostgreSQL database through PHP, should I embed the data in a

sql INSERT
statement within the function
pg_query
, or would it be more secure to pass the data through an array into the
pg_insert
function?

My guess would be
pg_insert
since it's just inserting the data directly into the table rather than querying the table and then inserting it.

Please visit http://php.net/manual/en/book.pgsql.php for reference to these 2 functions.

Answer

To avoid SQL injection, you want to call methods that accepts SQL parameters as method parameters. That way, your SQL library can make sure that the parameters are properly escaped. Never build your own SQL-statements by "appending" string-fragments. If you do, you are responsible for SQL-escaping the parameters. This is hard to get right.

Therefore, you should prefer pg_query_params (or pg_insert) over pg_query.

This is wrong (because you must remember to escape the $arg1-parameter. Which is hard.):

pg_query("INSERT INTO TABLE ... VALUES " . $arg1 . ";");

Right (because the smart guys at PostgresQL know how to escape your parameter):

pg_query_params("INSERT INTO TABLE ... VALUES $1", "FooBar");