SQL injection

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
34 messages Options
12
Reply | Threaded
Open this post in threaded view
|

SQL injection

Ben-Nes Yonatan
Hi all,

I'm currently trying to build a defence against SQL INJECTION, after
reading some material on it I arrived to few possible solutions and I
would like to know if anyone can comment anything about them or maybe
add a solution of its own:

1. PachyRand: SQL Randomization for the PostgreSQL JDBC Driver - seems
to be the best solution (easiest and most protective) though I didnt
understood entirely if the solution is available for production
enviorments or not, information can be attained at:
http://nsl.cs.columbia.edu/projects/pachyrand/ &
http://mice.cs.columbia.edu/getTechreport.php?techreportID=355&format=pdf&

2. Running for each data which will be used at the db checks with
regular expressions to find out if its valid, this method is quite
complicated to me (dont know regular expressions too much) and it
demands diffrent checks to each data field (with quite big problems at
open text data), at the end im afraid that holes will exist..

3. Running PHP functions like settype($data, 'integer') to be sure that
the data which arrive is at the correct format and to the text run
pg_escape_string($data), I suspect that this method wont block even
close to 100% of the attacks, just like the former option.

Another factor is the overhead to the system, I think that the previous
methods don't create much overhead but if anyone have another idea of
course it will also need to be efficent.

Any new ideas or comments will be received gladly.

Thanks in advance!
   Yonatan Ben-Nes

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

plexq
I don't know too much about this solutions, but It always seemed to me
that simple numeric validation plus magic-quotes will work just fine.

Simply validate any numeric input (or you can just quote it with
postgresql, and postgres will do it for you), and auto-escape any
string inputs (particularly quotes).  replace("'","\\'");

I haven't figured out an attack that will get past this simple scheme
yet, I would love for somebody to correct me, because my sites are
built on this simple premise ;).

Alex

On 10/31/05, Yonatan Ben-Nes <[hidden email]> wrote:

> Hi all,
>
> I'm currently trying to build a defence against SQL INJECTION, after
> reading some material on it I arrived to few possible solutions and I
> would like to know if anyone can comment anything about them or maybe
> add a solution of its own:
>
> 1. PachyRand: SQL Randomization for the PostgreSQL JDBC Driver - seems
> to be the best solution (easiest and most protective) though I didnt
> understood entirely if the solution is available for production
> enviorments or not, information can be attained at:
> http://nsl.cs.columbia.edu/projects/pachyrand/ &
> http://mice.cs.columbia.edu/getTechreport.php?techreportID=355&format=pdf&
>
> 2. Running for each data which will be used at the db checks with
> regular expressions to find out if its valid, this method is quite
> complicated to me (dont know regular expressions too much) and it
> demands diffrent checks to each data field (with quite big problems at
> open text data), at the end im afraid that holes will exist..
>
> 3. Running PHP functions like settype($data, 'integer') to be sure that
> the data which arrive is at the correct format and to the text run
> pg_escape_string($data), I suspect that this method wont block even
> close to 100% of the attacks, just like the former option.
>
> Another factor is the overhead to the system, I think that the previous
> methods don't create much overhead but if anyone have another idea of
> course it will also need to be efficent.
>
> Any new ideas or comments will be received gladly.
>
> Thanks in advance!
>    Yonatan Ben-Nes
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Ben Chobot
In reply to this post by Ben-Nes Yonatan
Maybe I'm not very creative, but it sure seems to me that if you escape your
strings, make sure your numbers are numbers, and your booleans are actually
booleans, then you're protected....

On Mon, 31 Oct 2005, Yonatan Ben-Nes wrote:

> Any new ideas or comments will be received gladly.
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Dan Sugalski
In reply to this post by Ben-Nes Yonatan
At 7:54 PM +0200 10/31/05, Yonatan Ben-Nes wrote:
>Hi all,
>
>I'm currently trying to build a defence against SQL INJECTION, after
>reading some material on it I arrived to few possible solutions and
>I would like to know if anyone can comment anything about them or
>maybe add a solution of its own:

Just out of curiosity, is this something that constant SQL (or SQL
generated by code) with placeholder variables won't protect against?
--
                                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
[hidden email]                         have teddy bears and even
                                       teddy bears get drunk

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

MaXX-2
In reply to this post by Ben-Nes Yonatan
Hi,

Yonatan Ben-Nes wrote:
> Hi all,
>
> I'm currently trying to build a defence against SQL INJECTION, after
> reading some material on it I arrived to few possible solutions and I
> would like to know if anyone can comment anything about them or maybe
> add a solution of its own:
[...]

If you're running PHP on an Apache server check mod_security, you'll have to
tune a little bit it's default ruleset, but it does a great job for me. It
will not protect you against tricks like 'chr(39)' (single quote) or
funnier 'cH%52(123-84)' unless you write specific rules. It inspect both
GET an POST payloads, performs unescaping and lots of other cool things
(protection agains shell command injection)...
Be warned, it has some side effects, as it will kick you off if you try to
publish SQL code on your site even if your code isn't malicious or
sentences that may look like sql (delete from something)...

As suggested, parameters validation is required, regexps are great for this
job and are easy to learn (do it you'll see). Do the validation in your PHP
before sending the query to your server (do not rely only on checking
inside stored procedures as the injection can take place before the actual
checking)
 
HTH,
--
MaXX

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

plexq
Can you demonstrate a URL/attack that would constitute an injection
attack that would get around magic-quotes, or provide some links to
such?

Alex

On 10/31/05, MaXX <[hidden email]> wrote:

> Hi,
>
> Yonatan Ben-Nes wrote:
> > Hi all,
> >
> > I'm currently trying to build a defence against SQL INJECTION, after
> > reading some material on it I arrived to few possible solutions and I
> > would like to know if anyone can comment anything about them or maybe
> > add a solution of its own:
> [...]
>
> If you're running PHP on an Apache server check mod_security, you'll have to
> tune a little bit it's default ruleset, but it does a great job for me. It
> will not protect you against tricks like 'chr(39)' (single quote) or
> funnier 'cH%52(123-84)' unless you write specific rules. It inspect both
> GET an POST payloads, performs unescaping and lots of other cool things
> (protection agains shell command injection)...
> Be warned, it has some side effects, as it will kick you off if you try to
> publish SQL code on your site even if your code isn't malicious or
> sentences that may look like sql (delete from something)...
>
> As suggested, parameters validation is required, regexps are great for this
> job and are easy to learn (do it you'll see). Do the validation in your PHP
> before sending the query to your server (do not rely only on checking
> inside stored procedures as the injection can take place before the actual
> checking)
>
> HTH,
> --
> MaXX
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

MaXX-2
Alex Turner wrote:

> Can you demonstrate a URL/attack that would constitute an injection
> attack that would get around magic-quotes, or provide some links to
> such?
>
[...]
Just quoting an article in Hackin9 (N°5/2005) I was just reading before
writing my post (page 53, translated from french): "The PHP function
magic_quote() allow to cancel automaticaly the effect of a single quote
using backslashes; however this function is used in conjunction with  the
function strip_slashes(), the escaping characters are suppressed."

I admit that I haven't tried and don't realy know how to implement this one
but I presume they have tried. I'll google on this to see if I can find
some demonstration as this is the first time I read this magazine.

After reading this article I tested some of the suggested attacks agains my
Perl CMS engine based on Pg and hopefully the given examples work with
MySQL but I'm reviewing my regexps just in case...

--
MaXX


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Jim C. Nasby
In reply to this post by Ben-Nes Yonatan
Does PHP support prepared queries with bound parameters for PostgreSQL?
Not only is that foolproof (unless you're calling a function that uses
an argument to build a query string...), you'll get a performance boost
as well since PostgreSQL won't have to reparse and plan every query.

On Mon, Oct 31, 2005 at 07:54:58PM +0200, Yonatan Ben-Nes wrote:

> Hi all,
>
> I'm currently trying to build a defence against SQL INJECTION, after
> reading some material on it I arrived to few possible solutions and I
> would like to know if anyone can comment anything about them or maybe
> add a solution of its own:
>
> 1. PachyRand: SQL Randomization for the PostgreSQL JDBC Driver - seems
> to be the best solution (easiest and most protective) though I didnt
> understood entirely if the solution is available for production
> enviorments or not, information can be attained at:
> http://nsl.cs.columbia.edu/projects/pachyrand/ &
> http://mice.cs.columbia.edu/getTechreport.php?techreportID=355&format=pdf&
>
> 2. Running for each data which will be used at the db checks with
> regular expressions to find out if its valid, this method is quite
> complicated to me (dont know regular expressions too much) and it
> demands diffrent checks to each data field (with quite big problems at
> open text data), at the end im afraid that holes will exist..
>
> 3. Running PHP functions like settype($data, 'integer') to be sure that
> the data which arrive is at the correct format and to the text run
> pg_escape_string($data), I suspect that this method wont block even
> close to 100% of the attacks, just like the former option.
>
> Another factor is the overhead to the system, I think that the previous
> methods don't create much overhead but if anyone have another idea of
> course it will also need to be efficent.
>
> Any new ideas or comments will be received gladly.
>
> Thanks in advance!
>   Yonatan Ben-Nes
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

plexq
I didn't think query plans were cached between sessions, in which case
prepeared statements aren't worth much for most HTTP based systems
(not counting luckily re-using the same connection using pgpool)...

Please correct me if I'm mistaken - I like being wrong ;)

Alex

On 10/31/05, Jim C. Nasby <[hidden email]> wrote:

> Does PHP support prepared queries with bound parameters for PostgreSQL?
> Not only is that foolproof (unless you're calling a function that uses
> an argument to build a query string...), you'll get a performance boost
> as well since PostgreSQL won't have to reparse and plan every query.
>
> On Mon, Oct 31, 2005 at 07:54:58PM +0200, Yonatan Ben-Nes wrote:
> > Hi all,
> >
> > I'm currently trying to build a defence against SQL INJECTION, after
> > reading some material on it I arrived to few possible solutions and I
> > would like to know if anyone can comment anything about them or maybe
> > add a solution of its own:
> >
> > 1. PachyRand: SQL Randomization for the PostgreSQL JDBC Driver - seems
> > to be the best solution (easiest and most protective) though I didnt
> > understood entirely if the solution is available for production
> > enviorments or not, information can be attained at:
> > http://nsl.cs.columbia.edu/projects/pachyrand/ &
> > http://mice.cs.columbia.edu/getTechreport.php?techreportID=355&format=pdf&
> >
> > 2. Running for each data which will be used at the db checks with
> > regular expressions to find out if its valid, this method is quite
> > complicated to me (dont know regular expressions too much) and it
> > demands diffrent checks to each data field (with quite big problems at
> > open text data), at the end im afraid that holes will exist..
> >
> > 3. Running PHP functions like settype($data, 'integer') to be sure that
> > the data which arrive is at the correct format and to the text run
> > pg_escape_string($data), I suspect that this method wont block even
> > close to 100% of the attacks, just like the former option.
> >
> > Another factor is the overhead to the system, I think that the previous
> > methods don't create much overhead but if anyone have another idea of
> > course it will also need to be efficent.
> >
> > Any new ideas or comments will be received gladly.
> >
> > Thanks in advance!
> >   Yonatan Ben-Nes
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
> --
> Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Matthew D. Fuller
In reply to this post by Ben Chobot
On Mon, Oct 31, 2005 at 10:12:45AM -0800 I heard the voice of
Ben, and lo! it spake thus:
> Maybe I'm not very creative, but it sure seems to me that if you
> escape your strings, make sure your numbers are numbers, and your
> booleans are actually booleans, then you're protected....

Once nice touch is that booleans and numbers will be accepted by Pg if
they're escaped and quoted like strings.  So, in PHP, I always run
EVERYTHING through a wrapper db_quote() function that returns
something like ("'" . pg_escape_string(foo) . "'") (or its equivalent
in MySQL, etc) and just call it fixed.  Of course, I do type checks
for user feedback and such as well, but in case something slips
through, it's all escaped.


--
Matthew Fuller     (MF4839)   |  [hidden email]
Systems/Network Administrator |  http://www.over-yonder.net/~fullermd/
           On the Internet, nobody can hear you scream.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

zorchon
Can some knowledgeable person set the record straight on SQL injection,
please?  I thought that the simple answer was to use prepared statements
with bind variables (except when you are letting the user specify whole
chunks of SQL, ugh), but there are many people posting who either don't
know about prepared statements or know something I don't.

Thanks,
Kevin Murphy

P.S.  I don't use PHP, but google informs me that PHP definitely has
prepared statement options: PEAR::DB, PDO in 5.X+, etc.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Tom Lane-2
Kevin Murphy <[hidden email]> writes:
> Can some knowledgeable person set the record straight on SQL injection,
> please?  I thought that the simple answer was to use prepared statements
> with bind variables (except when you are letting the user specify whole
> chunks of SQL, ugh), but there are many people posting who either don't
> know about prepared statements or know something I don't.

That's a good way to do it if you are using a client library that
supports it.  It's not the Only Way though.

In my mind the issue is not just whether a particular technique is
bulletproof; it's how sure you can be that you have not missed applying
it anywhere.  If you rely on applying an escaping function then it's
pretty easy to forget it in one or two places, and it only takes one
hole to be vulnerable :-(.  The nice thing about prepared statements is
that it's relatively easier to be sure you haven't messed up, because
the coding rule is pretty simple: if all SQL commands are constant
strings you're definitely safe.

The downside of course is that this approach doesn't handle all cases,
for instance if you have to generate AND/OR where-conditions on the fly
(think of a search engine for example).

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Jim C. Nasby
In reply to this post by plexq
On Mon, Oct 31, 2005 at 10:13:20PM -0500, Alex Turner wrote:
> I didn't think query plans were cached between sessions, in which case
> prepeared statements aren't worth much for most HTTP based systems
> (not counting luckily re-using the same connection using pgpool)...
>
> Please correct me if I'm mistaken - I like being wrong ;)

No, you're right, but if you're not using connection pooling you clearly
don't care about performance anyway...
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Scott Marlowe
On Tue, 2005-11-01 at 09:09, Jim C. Nasby wrote:
> On Mon, Oct 31, 2005 at 10:13:20PM -0500, Alex Turner wrote:
> > I didn't think query plans were cached between sessions, in which case
> > prepeared statements aren't worth much for most HTTP based systems
> > (not counting luckily re-using the same connection using pgpool)...
> >
> > Please correct me if I'm mistaken - I like being wrong ;)
>
> No, you're right, but if you're not using connection pooling you clearly
> don't care about performance anyway...

Depends on what you mean by performance.  I've written apps that were
used by one or two people at once, and spit out 100M at a shot for an
excel spread sheet or made huge 100 page pdfs.  They had to run fast,
but connection time wasn't an issue.  Since the average run time of
those scripts as 1 to 30 seconds, the connect time was absolutely not an
issue.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Jim C. Nasby
On Tue, Nov 01, 2005 at 11:19:12AM -0600, Scott Marlowe wrote:

> On Tue, 2005-11-01 at 09:09, Jim C. Nasby wrote:
> > On Mon, Oct 31, 2005 at 10:13:20PM -0500, Alex Turner wrote:
> > > I didn't think query plans were cached between sessions, in which case
> > > prepeared statements aren't worth much for most HTTP based systems
> > > (not counting luckily re-using the same connection using pgpool)...
> > >
> > > Please correct me if I'm mistaken - I like being wrong ;)
> >
> > No, you're right, but if you're not using connection pooling you clearly
> > don't care about performance anyway...
>
> Depends on what you mean by performance.  I've written apps that were
> used by one or two people at once, and spit out 100M at a shot for an
> excel spread sheet or made huge 100 page pdfs.  They had to run fast,
> but connection time wasn't an issue.  Since the average run time of
> those scripts as 1 to 30 seconds, the connect time was absolutely not an
> issue.

Hrm... what's that quote about stereotyping? :)

Granted, sometimes connection startup time doesn't matter. But in most
web environments (we are talking PHP here remember) you'll either be
using a connection pool or not caring at all about performance...
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Scott Marlowe
On Tue, 2005-11-01 at 12:12, Jim C. Nasby wrote:

> On Tue, Nov 01, 2005 at 11:19:12AM -0600, Scott Marlowe wrote:
> > On Tue, 2005-11-01 at 09:09, Jim C. Nasby wrote:
> > > On Mon, Oct 31, 2005 at 10:13:20PM -0500, Alex Turner wrote:
> > > > I didn't think query plans were cached between sessions, in which case
> > > > prepeared statements aren't worth much for most HTTP based systems
> > > > (not counting luckily re-using the same connection using pgpool)...
> > > >
> > > > Please correct me if I'm mistaken - I like being wrong ;)
> > >
> > > No, you're right, but if you're not using connection pooling you clearly
> > > don't care about performance anyway...
> >
> > Depends on what you mean by performance.  I've written apps that were
> > used by one or two people at once, and spit out 100M at a shot for an
> > excel spread sheet or made huge 100 page pdfs.  They had to run fast,
> > but connection time wasn't an issue.  Since the average run time of
> > those scripts as 1 to 30 seconds, the connect time was absolutely not an
> > issue.
>
> Hrm... what's that quote about stereotyping? :)
>
> Granted, sometimes connection startup time doesn't matter. But in most
> web environments (we are talking PHP here remember) you'll either be
> using a connection pool or not caring at all about performance...

What's that quote about all generalizations?  :)

In all honesty, in a corporate intranet for about 1500 users, with an
average of 10 to 20 logged in at a time, our average page response time
was well under a second, and server was rock solid and stable.  Our
effort was better spent elsewhere, like tuning SQL queries, than
worrying about connection pooling.  It was more a solution in search of
a problem.  This on a site that connected to the database for almost
every page it delivered.

Now, if it was for amazon.com s front page, well, of course, connection
times might well have meant a lot more.  But it wasn't, and performance
was important.  We just didn't have 1,000 users pounding on the door for
a drink of water, we had dozens going to the well with 5 gallon buckets.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Ben-Nes Yonatan
In reply to this post by Jim C. Nasby
Jim C. Nasby wrote:

> Does PHP support prepared queries with bound parameters for PostgreSQL?
> Not only is that foolproof (unless you're calling a function that uses
> an argument to build a query string...), you'll get a performance boost
> as well since PostgreSQL won't have to reparse and plan every query.
>
> On Mon, Oct 31, 2005 at 07:54:58PM +0200, Yonatan Ben-Nes wrote:
>
>>Hi all,
>>
>>I'm currently trying to build a defence against SQL INJECTION, after
>>reading some material on it I arrived to few possible solutions and I
>>would like to know if anyone can comment anything about them or maybe
>>add a solution of its own:
>>
>>1. PachyRand: SQL Randomization for the PostgreSQL JDBC Driver - seems
>>to be the best solution (easiest and most protective) though I didnt
>>understood entirely if the solution is available for production
>>enviorments or not, information can be attained at:
>>http://nsl.cs.columbia.edu/projects/pachyrand/ &
>>http://mice.cs.columbia.edu/getTechreport.php?techreportID=355&format=pdf&
>>
>>2. Running for each data which will be used at the db checks with
>>regular expressions to find out if its valid, this method is quite
>>complicated to me (dont know regular expressions too much) and it
>>demands diffrent checks to each data field (with quite big problems at
>>open text data), at the end im afraid that holes will exist..
>>
>>3. Running PHP functions like settype($data, 'integer') to be sure that
>>the data which arrive is at the correct format and to the text run
>>pg_escape_string($data), I suspect that this method wont block even
>>close to 100% of the attacks, just like the former option.
>>
>>Another factor is the overhead to the system, I think that the previous
>>methods don't create much overhead but if anyone have another idea of
>>course it will also need to be efficent.
>>
>>Any new ideas or comments will be received gladly.
>>
>>Thanks in advance!
>>  Yonatan Ben-Nes
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: don't forget to increase your free space map settings
>>
>
>

Won't that create a performance penalty to extremly dynamic sites cause
the plan will be planned only once and the data may vary alot?
Beside that I still won't have a solution to places where I create a
query which can vary alot (JOIN diffrent tables, diffrent WHERE etc...),
it doesn't seem logical to me to start and create all of the diffrent
possibilites of queries when I create such an option at a site.

Thanks alot everyone and sorry for posting something and then not
returning for so long (though everything seem like rolling alone nicely :)).
   Yonatan Ben-Nes

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Jim C. Nasby
On Tue, Nov 01, 2005 at 08:27:21PM +0200, Yonatan Ben-Nes wrote:
> Won't that create a performance penalty to extremly dynamic sites cause
> the plan will be planned only once and the data may vary alot?
> Beside that I still won't have a solution to places where I create a
> query which can vary alot (JOIN diffrent tables, diffrent WHERE etc...),
> it doesn't seem logical to me to start and create all of the diffrent
> possibilites of queries when I create such an option at a site.

Yes, when you start getting into dynamically generated SQL you quickly
loose the performance benefit of prepared statements just because odds
are good that nothing else will use it. But you still have the benefit
of bound parameters and protection from injection.
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Dan Sugalski
In reply to this post by Tom Lane-2
At 8:57 AM -0500 11/1/05, Tom Lane wrote:
>Kevin Murphy <[hidden email]> writes:
>>  Can some knowledgeable person set the record straight on SQL injection,
>>  please?  I thought that the simple answer was to use prepared statements
>>  with bind variables (except when you are letting the user specify whole
>>  chunks of SQL, ugh), but there are many people posting who either don't
>  > know about prepared statements or know something I don't.

[snippage]

>The nice thing about prepared statements is
>that it's relatively easier to be sure you haven't messed up, because
>the coding rule is pretty simple: if all SQL commands are constant
>strings you're definitely safe.
>
>The downside of course is that this approach doesn't handle all cases,
>for instance if you have to generate AND/OR where-conditions on the fly
>(think of a search engine for example).

The lack of entirely-constant SQL isn't necessarily a barrier to
using prepared statements and bound variables -- I'm doing this in a
pretty large app, and it's not that big a deal to safely and
dynamically generate the SQL and still use bound variables.

The single biggest issue it that the planner has no access to the
data in the where clause, so it unconditionally rules out some paths.
(LIKE clauses with constant prefix text, like "FOO%", get hit hard by
this)

It's certainly not as simple as all-constant SQL text, but it's
definitely doable, and worth looking into.
--
                                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
[hidden email]                         have teddy bears and even
                                       teddy bears get drunk

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: SQL injection

Ben-Nes Yonatan
In reply to this post by Jim C. Nasby
Jim C. Nasby wrote:

> On Tue, Nov 01, 2005 at 08:27:21PM +0200, Yonatan Ben-Nes wrote:
>
>>Won't that create a performance penalty to extremly dynamic sites cause
>>the plan will be planned only once and the data may vary alot?
>>Beside that I still won't have a solution to places where I create a
>>query which can vary alot (JOIN diffrent tables, diffrent WHERE etc...),
>>it doesn't seem logical to me to start and create all of the diffrent
>>possibilites of queries when I create such an option at a site.
>
>
> Yes, when you start getting into dynamically generated SQL you quickly
> loose the performance benefit of prepared statements just because odds
> are good that nothing else will use it. But you still have the benefit
> of bound parameters and protection from injection.

My problem with the dynamically generated SQL is that I'll have to
create and maintain lots of prepared statements and be sure that I dont
miss any available option, and also every time that ill have to do basic
changes at the queries I'll have to update each one of those prepared
statements.... it seems to me like of extra work for sites which can
create many dynamic queries.

And about the performance penalty, I don't really care about losing the
benefit of prepared statements, I'm actually more afraid of receiving
penalty of using them... the following is quoted from the manual:
"In some situations, the query plan produced for a prepared statement
will be inferior to the query plan that would have been chosen if the
statement had been submitted and executed normally. This is because when
the statement is planned and the planner attempts to determine the
optimal query plan, the actual values of any parameters specified in the
statement are unavailable. PostgreSQL collects statistics on the
distribution of data in the table, and can use constant values in a
statement to make guesses about the likely result of executing the
statement. Since this data is unavailable when planning prepared
statements with parameters, the chosen plan may be suboptimal."

Thanks again,
   Yonatan Ben-Nes

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
12