RE: Safe handling of an SQL query

Have your webservice connect to the db as an unprivileged user that has only select privileges.


-----Original Message-----
From: Vincent Veyron [mailto:[email protected]] 
Sent: April-16-12 8:55 AM
To: [email protected]
Subject: Safe handling of an SQL query

Hi Group,

I maintain a business application that uses a LAMP stack of Linux +
Apache2 + Mod_perl + Postgresql. One recurring problem I have is that each client wants his own set of custom reports using queries from the database.

This is currently covered via a table in the database which holds the query associated with the report, but that quickly leads to a maintenance problem.

I am thinking of creating a sort of web service, where my customers can send a query to the server, via a VB or .NET procedure launched on the opening of a document (.doc, .odf, other ) and I'll just serve the dataset resulting from the query. 

My question is :

Can I make sure that whatever query is sent to the server, it will only be a SELECT <...> and _never_ a UPDATE or INSERT or DELETE ?

I can check with a regexp, but I am worried about the possibility to encode terms of the query into something obscure enough that it'll go through. For instance, DELETE in hexadecimal looks like this :
44454c4554450d0a



--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique