Read-only db access to prevent SQL injection attacks

An idea for safer db applications

We often read in articles covering performance aspects of a db-driven application, or a database engine itself, that READs are usually more frequent than WRITEs.
That's obvious and true for most web sites / web applications, but we rarely think about this fact when we design our app.
I mean, we may consider this fact when choosing a dbms (or a db engine) over another, but that's it.

I haven't seen many (if any) open source projects leveraging this fact for improving the security of the application.
For instance, we don't need a connection with WRITE rights for a search form, or a select menu. Why can't we use a read-only db connection for those tasks? And a read/write one only when we really need it?

I agree that writing an application using the same connection all over is easier, but using two connections (one with R/W rights, and one with R/O rights, depending on the function where we use it) isn't terribly difficult. Writing a singleton function returning a db connection with R/W or R/O permissions (depending on the specific need of the current task) is as easy as this example (using PEAR::MDB2 ):

function getConnection($with_write_rights=false)
    //DSN of user having read/write grant
    static $rw_dsn = 'phptype://rw_username:rw_password@hostspec/database';
    //DSN of user having read-only grant
    static $ro_dsn = 'phptype://ro_username:ro_password@hostspec/database';
    if ($with_write_rights) {
        return MDB2::singleton($rw_dsn);
    return MDB2::singleton($ro_dsn);

where rw_username/rw_password are the login details for an user having full rights on the db, while the user identified by ro_username/ro_password has a read-only GRANT.

Having a fine-grained approach (i.e. restricting WRITE access only to a given subset of db tables) would be even better, but not always feasible.



While this method doesn't excuse you from escaping any data you pass to the database, and/or using prepared statements where available, you could avoid the SQL injection attacks using a read-only connection where you don't need to write to the database.
N.B.: as Marc Gear suggested in a nice mail, a read-only db connection is still subject to content stealing (usernames, passwords, table structures, etc). Again, what I'm suggesting here is an extra layer of protection, but don't ever forget to escape your queries!

Couldn't this principle be applied to -say- the various CMS / forums / blog systems? I'd love to hear your opinions on this matter, feel free to drop me a mail.
Prevent SQL injection attaks: GRANT read-only privileges everywhere it's possible

Lorenzo Alberton

Lorenzo Alberton Lorenzo has been working with large enterprise UK companies for the past 10+ years and is currently CTO at DataSift. He's an international conference speaker and a long-time contributor to many open source projects. Lorenzo Alberton's profile on GitHub Lorenzo Alberton's profile on LinkedIN View Lorenzo Alberton's profile on PHP PEAR
View Lorenzo Alberton's Twitter stream Lorenzo Alberton - Sun Certified MySQL 5 Developer PHP5 ZCE - Zend Certified Engineer


AJAX, Apache, Book Review, Charset, Cheat Sheet, Data structures, Database, Firebird SQL, Hadoop, Imagick, INFORMATION_SCHEMA, JavaScript, Kafka, Linux, Message Queues, mod_rewrite, Monitoring, MySQL, NoSQL, Oracle, PDO, PEAR, Performance, PHP, PostgreSQL, Profiling, Scalability, Security, SPL, SQL Server, SQLite, Testing, Tutorial, TYPO3, Windows, Zend Framework
Buy me a book - Introduction To Information Retrieval