Hi Andreas,
I guess you forgot to cc the list.
--- Begin Message ---
Hi Vincent,
just my 2 cents:
a) Use a db user only with select rights for this reporting stuff.
b) You should always keep the control over the selects which are
fired against your db. Otherwise someone can bring down the db
very easy. The advantages are:
1) You know which selects are made and the impact of them
2) From the business perspective: You have control over the
outcome of the reports. I saw more than one company with
a freedom of report construction and many different reports
with the same semantic label. E.g. a trunover report with
different numbers. IMHO earlier than later you need a kind
of report repository anyway.
Best regards
Andreas
> -----Original Message-----
> From: Vincent Veyron [mailto:vv.li...@wanadoo.fr]
> Sent: Monday, April 16, 2012 3:55 PM
> To: modperl@perl.apache.org
> 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
--- End Message ---
