RE: highscalability.com report

[Vincent Veyron]

Le jeudi 12 avril 2012 à 13:14 -0400, eric.b...@barclays.com a écrit :
> Well, finding (good) developers is certainly an issue.
> 

Over the years, I have seen more than one of those being driven out of
the field by the inane management that most developers toil under. And
considering how demanding it is to be a good programmer, I can see why
they give up : you just can't have both.

On the other hand, I see scores of good developers in open source
projects (their products certainly are very good)


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

Safe handling of an SQL query

[Vincent Veyron]

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

Re: Safe handling of an SQL query

[Robert Aspinall]

I suggest you limit that functionality at the database level.  You should
be able to make sure their accounts can only perform SELECT queries.  This
is much safer than attempting to detect malicious/improper SQL.

Robert Aspinall

NOAA's National Ocean Service
CO-OPS/Information Systems Division
1305 East-West Highway
Bldg. SSMC4, Station Id 6314
Silver Spring, MD 20910

robert.aspin...@noaa.gov



On Mon, Apr 16, 2012 at 9:55 AM, Vincent Veyron  wrote:

> 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
>
>

RE: Safe handling of an SQL query

[Lloyd Richardson]

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


-Original Message-
From: Vincent Veyron [mailto:vv.li...@wanadoo.fr] 
Sent: April-16-12 8:55 AM
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

Re: Safe handling of an SQL query

[Jiří Pavlovský]

On 16.4.2012 15:55, Vincent Veyron wrote:


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
?





In addition to already mentioned approaches you could also have a look 
at the "ReadOnly" attribute supported by DBI:


"   An application can set the "ReadOnly" attribute of a handle to a 
true value to indicate that it will not be attempting to

   make any changes using that handle or any children of it.
"

--
Jiří Pavlovský

[Fwd: RE: Safe handling of an SQL query]

[Vincent Veyron]

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 ---

Re: Safe handling of an SQL query

[Bruce Johnson]

I'm going to suggest going another direction...what you (and they) want are a 
BI system. You can present a set of reports and the system provides for their 
ability to do ad hoc reporting and such like.







These sorts of things let the end user have at the data, but not in ways they 
can mess anythign up, and with the proper DB design they won't get the wrong 
answers from their data...


-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

unsubscribe

[Mauritz Hansen]

unsubscribe

[Mauritz Hansen]

unsubscribe

[Boston, Mike]

unsubscribe

Re: [Fwd: RE: Safe handling of an SQL query]

[demerphq]

On 16 April 2012 18:06, Vincent Veyron  wrote:
> 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.

You might want to look into setting up a slow query killer. Then you
dont have to worry about this.

Yves

-- 
perl -Mre=debug -e "/just|another|perl|hacker/"

Re: Safe handling of an SQL query

[Vincent Veyron]

Le lundi 16 avril 2012 à 10:45 -0700, Bruce Johnson a écrit :
> I'm going to suggest going another direction...what you (and they) want are a 
> BI system. You can present a set of reports and the system provides for their 
> ability to do ad hoc reporting and such like.
> 
> 
> 
> 
> 
> 
> 
> These sorts of things let the end user have at the data, but not in ways they 
> can mess anythign up, and with the proper DB design they won't get the wrong 
> answers from their data...
> 
> 

Interesting, thanks. 

I guess (in the message I forwarded to the list) Andreas is right,
though : the only way to be safe is to keep control of the query,
therefore keep it on the server.

I am doing this now, but passing parameters to the query becomes
cumbersome :-(



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