Lorenzo Alberton

London, UK   ·   Contact me   ·  

« Articles

Read-only db access to prevent SQL injection attacks

PHP, Database, Security   ·   06 June 2006 08:21

Abstract: An idea for safer db applications: use read-only db access wherever possible. Use GRANT to create an user with read-only rights, and use it 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.

Reference

Conclusion

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

Back

Related articles

Latest articles

Filter articles by topic

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

Back