Escape SQL queries in PHP PostgreSQL

Question

I have a website with lots of PHP files (really a lot...), which use the pg_query and pg_exec functions which do not escape the apostrophe in Postgre SQL queries.

However, for security reasons and the ability to store names with apostrophe in my database I want to add an escaping mechanism for my database input. A possible solution is to go through every PHP file and change the pg_query and pg_exec to use pg_query_params but it is both time consuming and error prone. A good idea would be to somehow override the pg_query and pg_exec to wrapper functions that would do the escaping without having to change any PHP file but in this case I guess I will have to change PHP function definitions and recompile it which is not very ideal.

So, the question is open and any ideas that would allow to do what I want with minimum time consumption are very welcome.

Answer 1

You post no code but I guess you have this:

$name = "O'Brian";
$result = pg_query($conn, "SELECT id FROM customer WHERE name='{$name}'");

... and you'd need to have this:

$name = "O'Brian";
$result = pg_query_params($conn, 'SELECT id FROM customer WHERE name=$1', array($name));

... but you think the task will consume an unreasonable amount of time.

While it's certainly complex, what alternatives do you have? You cannot override pg_query() but it'd be extremely simple to search and replace it for my_pg_query(). And now what? Your custom function will just see strings:

SELECT id FROM customer WHERE name='O'Brian'
SELECT id FROM customer WHERE name='foo' OR '1'='1'

Even if you manage to implement a bug-free SQL parser:

1. It won't work reliably with invalid SQL.
2. It won't be able to determine whether the query is the product of intentional SQL injection.

Just take it easy and fix queries one by one. It'll take time but possibly not as much as you think. Your app will be increasingly better as you progress.