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.

so, you are calling your database from alot php files? and want to overwrite php functions to save you the hassle to rewrite it securely? please dont do so. This could be the time to rework your database in general. Because a lot PHP files dont mean all of them need to access the database directly (MVC) — Najzero
– Najzero, Commented Dec 20, 2012 at 10:26
@Dr.Dan OP asks for escaping SQL queries while using PostgreSQL, right? So then its answer would be to use something like the answers stated in the question I linked, no? — Shyam K
– Shyam K, Commented Dec 20, 2012 at 10:37
@ShyamK No, it wouldn't. Read the problem again carefuly. — shadyyx
– shadyyx, Commented Dec 20, 2012 at 10:40

Álvaro González · Accepted Answer · 2012-12-20 17:38:02Z

3

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:

It won't work reliably with invalid SQL.
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.

edited Dec 20, 2012 at 17:38

answered Dec 20, 2012 at 10:45

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andy Lester Over a year ago

"but you think the task will consume an unreasonable amount of time" means that it is premature optimization. Rule 1 of Optimization Club is "do not optimize". Rule 2 of Optimization Club is "do not optimize without measuring first."

Andy Lester Over a year ago

No, it's not. It is the concern that quoting somehow will make the database access slower that is premature optimization.

Álvaro González Over a year ago

@AndyLester Oh, that... I meant that it'll take time to fix the code, which is the OP's concern.

Matt Gibson · Accepted Answer · 2012-12-20 10:46:02Z

This is a perfect example of when a database layer and associated API will save you loads of time. A good solution would be to make a DB class as a singleton, which you can instantiate from anywhere in your app. A simple set of wrapper functions will allow you to make all queries to the DB go through one point, so you can then alter the way they work very easily. You can also change from one DB to another, or from one DB vendor to another without touching the rest of the app.

The problem you are having with escaping is properly solved by using the PDO interface, instead of functions like pg_query(), which makes escaping unnecessary. Seeing as you'll have to alter everywhere in your app that uses the DB, you may as well refactor to use this pattern at the same time as it'll be the same amount of work.

class db_wrapper {

    // Singleton stuff
    private $instance;

    private function __construct() {
        // Connect to DB and store connection somewhere
    }

    public static function get_db() {
        if (isset($instance)) {
            return $instance;
        }
        return $instance = new db_wrapper();
    }

    // Public API

    public function query($sql, array $vars) {
        // Use PDO to connect to database and execute query
    }

}

// Other parts of your app look like this:

function do_something() {
    $db = db_wrapper::get_db();
    $sql = "SELECT * FROM table1 WHERE column = :name";
    $params = array('name' => 'valuename');
    $result = $db->query($sql, $params);

    // Use $result for something. 
}

Collectives™ on Stack Overflow

Escape SQL queries in PHP PostgreSQL

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related