Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: ModPerl: ModPerl

Safe handling of an SQL query

 

 

ModPerl modperl RSS feed   Index | Next | Previous | View Threaded


vv.lists at wanadoo

Apr 16, 2012, 6:55 AM

Post #1 of 17 (1625 views)
Permalink
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


robert.aspinall at noaa

Apr 16, 2012, 6:57 AM

Post #2 of 17 (1582 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

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.Aspinall [at] noaa



On Mon, Apr 16, 2012 at 9:55 AM, Vincent Veyron <vv.lists [at] wanadoo> 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
>
>


lloyd at protectchildren

Apr 16, 2012, 7:00 AM

Post #3 of 17 (1583 views)
Permalink
RE: Safe handling of an SQL query [In reply to]

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


-----Original Message-----
From: Vincent Veyron [mailto:vv.lists [at] wanadoo]
Sent: April-16-12 8:55 AM
To: modperl [at] perl
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


jira at getnet

Apr 16, 2012, 7:43 AM

Post #4 of 17 (1577 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

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ý


johnson at pharmacy

Apr 16, 2012, 10:45 AM

Post #5 of 17 (1577 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

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.

<http://www.softwareforenterprise.us/2009/12/21/list-of-top-open-source-business-intelligence-bi-software-solutions/>

<http://www.pentaho.com/>
<http://www.jaspersoft.com/>
<http://www.icCube.com/>

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


vv.lists at wanadoo

Apr 16, 2012, 1:21 PM

Post #6 of 17 (1574 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

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.
>
> <http://www.softwareforenterprise.us/2009/12/21/list-of-top-open-source-business-intelligence-bi-software-solutions/>
>
> <http://www.pentaho.com/>
> <http://www.jaspersoft.com/>
> <http://www.icCube.com/>
>
> 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


milu71 at gmx

Apr 17, 2012, 11:10 AM

Post #7 of 17 (1562 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Bonjour Vincent,

Vincent Veyron schrieb am 16.04.2012 um 22:21 (+0200):
>
> 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 :-(

If you haven't done that already, you could take a look at the following
three modules. While I have never used any of them I've bookmarked them
for future opportunities …

https://metacpan.org/module/SQL::Interp
https://metacpan.org/module/SQL::Abstract
https://metacpan.org/module/DBIx::Simple

Best,

Michael


vv.lists at wanadoo

Apr 17, 2012, 3:09 PM

Post #8 of 17 (1562 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Le mardi 17 avril 2012 à 20:10 +0200, Michael Ludwig a écrit :
> Bonjour Vincent,
>
> Vincent Veyron schrieb am 16.04.2012 um 22:21 (+0200):
> >
> > 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 :-(
>
> If you haven't done that already, you could take a look at the following
> three modules. While I have never used any of them I've bookmarked them
> for future opportunities …
>
> https://metacpan.org/module/SQL::Interp
> https://metacpan.org/module/SQL::Abstract
> https://metacpan.org/module/DBIx::Simple
>

Guten Tag Michael,

I did not know about those, but what I don't see the benefit compared to
using DBI directly, which is really concise. This is all I need to get a
reference to a data set :

my $dbh = $r->pnotes('dbh_data');

my $sql = 'SELECT ... FROM ... WHERE X=? AND Y=?';

#collect data
eval { $data = $dbh->selectall_arrayref($sql, { Slice => {} },
( $param_x, $param_y ) ) };


where dbh_data is a reference to a dbi connection using connect_cached,
stored in pnotes.

?

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


eric.berg at barclays

Apr 17, 2012, 3:29 PM

Post #9 of 17 (1570 views)
Permalink
RE: Safe handling of an SQL query [In reply to]

Vincent,

Don't make the mistake of thinking that you're not directly using DBI.

These modules provide an abstraction for creating SQL that is data driven. They free you from the error-prone string manipulation process, and allow you to build your queries in such a way as to be able to handle a variety of data.

For example, from the SQL::Abstract mod docs:

my $sql = SQL::Abstract->new;
my $table = 'widgets';
my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order);

Now you can use this for any table selecting any fields with or without specifying a where clause or an order by clause.

This allows you to directly create a quick hash to represent your where clause.

So, to get a SQL statement where first_name = 'Sally', last_name starts with "S", your %where hash would look like this:

%where = (first_name => 'Sally',
Last_name => { like => 'S%'});

Which would set the $stmt variable above to

select * from widgets where first_name = ? and last_name like ?

And the @bind would be

@bind = ('Sally', 'S%')

Very helpful! Very flexible! And no more of that messy string manipulation and escaping quotes.

Oh, and of course, the next thing you do is:

my $sth = $dbh->prepare($stmt);
$sth->execute(\@bind);

Which is where you directly use DBI.

Most all of this stuff is just an abstraction layer for creating SQL in a data-centric way.

> -----Original Message-----
> From: Vincent Veyron [mailto:vv.lists [at] wanadoo]
> Sent: Tuesday, April 17, 2012 6:09 PM
> To: Michael Ludwig
> Cc: modperl [at] perl
> Subject: Re: Safe handling of an SQL query
>
> Le mardi 17 avril 2012 à 20:10 +0200, Michael Ludwig a écrit :
> > Bonjour Vincent,
> >
> > Vincent Veyron schrieb am 16.04.2012 um 22:21 (+0200):
> > >
> > > 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 :-(
> >
> > If you haven't done that already, you could take a look at the
> following
> > three modules. While I have never used any of them I've bookmarked
> them
> > for future opportunities …
> >
> > https://metacpan.org/module/SQL::Interp
> > https://metacpan.org/module/SQL::Abstract
> > https://metacpan.org/module/DBIx::Simple
> >
>
> Guten Tag Michael,
>
> I did not know about those, but what I don't see the benefit compared
> to
> using DBI directly, which is really concise. This is all I need to get
> a
> reference to a data set :
>
> my $dbh = $r->pnotes('dbh_data');
>
> my $sql = 'SELECT ... FROM ... WHERE X=? AND Y=?';
>
> #collect data
> eval { $data = $dbh->selectall_arrayref($sql, { Slice => {} },
> ( $param_x, $param_y ) ) };
>
>
> where dbh_data is a reference to a dbi connection using connect_cached,
> stored in pnotes.
>
> ?
>
> --
> Vincent Veyron
> http://marica.fr/
> Logiciel de gestion des sinistres assurances et des dossiers
> contentieux pour le service juridique


_______________________________________________
Barclays is one of the world's leading banks, and we believe that by continuing to integrate the organisation we can better deliver the full power of Barclays to customers, clients and the communities in which we work.
As a visible sign of that integration we are moving to a single Barclays brand for the majority of our divisions, including those formerly known as Barclays Capital, Barclays Wealth and Barclays Corporate.

_______________________________________________

This e-mail may contain information that is confidential, privileged or otherwise protected from
disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute
it by any means. Please delete it and any attachments and notify the sender that you have received
it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a
solicitation to buy or sell any securities, investment products or other financial product or
service, an official confirmation of any transaction, or an official statement of Barclays. Any
views or opinions presented are solely those of the author and do not necessarily represent those
of Barclays. This e-mail is subject to terms available at the following link: www.barcap.com/emaildisclaimer.
By messaging with Barclays you consent to the foregoing. Barclays offers premier investment banking
products and services to its clients through Barclays Bank PLC, a company registered in England
(number 1026167) with its registered office at 1 Churchill Place, London, E14 5HP. This email may
relate to or be sent from other members of the Barclays Group.

_______________________________________________


milu71 at gmx

Apr 17, 2012, 3:30 PM

Post #10 of 17 (1564 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Vincent Veyron schrieb am 18.04.2012 um 00:09 (+0200):
> Le mardi 17 avril 2012 20:10 +0200, Michael Ludwig a crit :
> > Vincent Veyron schrieb am 16.04.2012 um 22:21 (+0200):
> > >
> > > I am doing this now, but passing parameters to the query becomes
> > > cumbersome :-(
> >
> > https://metacpan.org/module/SQL::Interp
> > https://metacpan.org/module/SQL::Abstract
> > https://metacpan.org/module/DBIx::Simple

> I did not know about those, but what I don't see the benefit compared
> to using DBI directly, which is really concise.

I'm mostly happy using DBI directly, too. The modules I listed do add
a convenience layer on top, though. Which is what I thought you were
looking for; guess I simply got you wrong when you said that passing
parameters was becoming cumbersome. The modules in question strive to
make your life easier. Maybe people can come up with more helpful
suggestions if you post a concrete example of what is cumbersome.

Best,

Michael


vv.lists at wanadoo

Apr 17, 2012, 5:11 PM

Post #11 of 17 (1566 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Le mercredi 18 avril 2012 00:30 +0200, Michael Ludwig a crit :
> Vincent Veyron schrieb am 18.04.2012 um 00:09 (+0200):

> Maybe people can come up with more helpful
> suggestions if you post a concrete example of what is cumbersome.
>

Sure, the app in my sig has a demo a account which you can use for
that.

This is an application for the management of insurance claims and legal
cases. Go into any 'Dossier' (French for folder/case) and hit the
'Tiers' tab (French for 'Third Party') at the top of the screen.

There should be at least one person in there, if not you can add one;
click on 'Afficher les modles' in that person's info.

You'll find two models : one is a Word document (not to open directly in
IE, must be downloaded first and then opened), the other one an
OpenOffice document (which you can open directly).

You'll see that the models auto-fill (merges actually) with data taken
from the database. The data is written to a generic file when you hit
the link, and that file is fetched via a VB procedure on the document's
opening. The query associated with the model is stored on the server and
never sent to the client who only sees the resulting data set.

This works because no additional input is required to fill the models.
But for statistical reports for instance, various inputs from the user
are often required (what year for instance) and there is no place to put
them in my setup. Maintaining a table and a form to manage all sorts of
parameters associated with various reports just seems impractical.

That is the reason I was looking for a way to put the query inside the
report, so that it could be sent with the proper parameters to the
server. That however has other problems, as seen upthread.

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


milu71 at gmx

Apr 18, 2012, 3:39 PM

Post #12 of 17 (1559 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Vincent Veyron schrieb am 18.04.2012 um 02:11 (+0200):
> Le mercredi 18 avril 2012 à 00:30 +0200, Michael Ludwig a écrit :
>
> > Maybe people can come up with more helpful
> > suggestions if you post a concrete example of what is cumbersome.
> >
>
> Sure, the app in my sig has a demo a account which you can use for
> that.
>
> This is an application for the management of insurance claims and
> legal cases. Go into any 'Dossier' (French for folder/case) and hit
> the 'Tiers' tab (French for 'Third Party') at the top of the screen.
>
> There should be at least one person in there, if not you can add one;
> click on 'Afficher les modèles' in that person's info.

Got lost here, but your description makes sense even without the real
thing, so …

> But for statistical reports for instance, various inputs from the user
> are often required (what year for instance) and there is no place to
> put them in my setup. Maintaining a table and a form to manage all
> sorts of parameters associated with various reports just seems
> impractical.
>
> That is the reason I was looking for a way to put the query inside the
> report, so that it could be sent with the proper parameters to the
> server. That however has other problems, as seen upthread.

It sounds like you want to have some sort of webservice for this to work
without coupling the client documents too tightly to the server backend.
At least that's what I would consider doing given my understanding of
the problem (which may be wrong).

Best,

Michael


vv.lists at wanadoo

Apr 19, 2012, 12:24 AM

Post #13 of 17 (1557 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Le jeudi 19 avril 2012 à 00:39 +0200, Michael Ludwig a écrit :

> Got lost here, but your description makes sense even without the real
> thing, so …


Not sure how you got lost, did you not find the site? the address is
below, in my signature. Then enter the demo account.

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


milu71 at gmx

Apr 19, 2012, 9:38 AM

Post #14 of 17 (1553 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Vincent Veyron schrieb am 19.04.2012 um 09:24 (+0200):
> Le jeudi 19 avril 2012 à 00:39 +0200, Michael Ludwig a écrit :
>
> > Got lost here, but your description makes sense even without the
> > real thing, so …
>
>
> Not sure how you got lost, did you not find the site? the address is
> below, in my signature. Then enter the demo account.

Did all that. Got lost at this point:

> There should be at least one person in there, if not you can add one;
> click on 'Afficher les modèles' in that person's info.

I couldn't find any persons or their info or "Afficher les modèles".
These things simply weren't there.

Michael


vv.lists at wanadoo

Apr 19, 2012, 1:33 PM

Post #15 of 17 (1552 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Le jeudi 19 avril 2012 18:38 +0200, Michael Ludwig a crit :

> Did all that. Got lost at this point:
>

> I couldn't find any persons or their info or "Afficher les modles".
> These things simply weren't there.


I see, I forgot to tell one step : once in the demo account, you see a
list a 'Dossiers' in the right part of the screen

Click into any 'dossier', the first one will do. Once inside, you'll see
the tabs at the top of the screen, choose 'Tiers' or 'Intervenants'. The
links are in that person's info.


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


milu71 at gmx

Apr 20, 2012, 2:55 AM

Post #16 of 17 (1558 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Vincent Veyron schrieb am 19.04.2012 um 22:33 (+0200):
>
> I see, I forgot to tell one step : once in the demo account, you see a
> list a 'Dossiers' in the right part of the screen
>
> Click into any 'dossier', the first one will do. Once inside, you'll see
> the tabs at the top of the screen, choose 'Tiers' or 'Intervenants'. The
> links are in that person's info.

Okay. As for the problem of passing parameters, I would just post the
input form as XML or whatever is handy for you and let the service
figure out what to do with that data based on, say, the document
element. So each type of document would have an associated server-side
handler that knows how to deal with it and what data to send back.
Pretty general, but that's how I'd tackle it. Hope this helps.

Best,

Michael


vv.lists at wanadoo

Apr 20, 2012, 5:12 AM

Post #17 of 17 (1554 views)
Permalink
Re: Safe handling of an SQL query [In reply to]

Le vendredi 20 avril 2012 11:55 +0200, Michael Ludwig a crit :
> element. So each type of document would have an associated server-side
> handler that knows how to deal with it and what data to send back.

The problem with this solution is that it leads to a maintenance
nightmare, as these documents tend to change on the whims of various
kinds of people. They are numerous, too.

Thanks for looking.

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

ModPerl modperl RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.