Thread: SQL injection
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
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 <da@canaan.co.il> 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
>
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. >
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
dan@sidhe.org have teddy bears and even
teddy bears get drunk
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
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 <bs139412@skynet.be> 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 >
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
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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 <jnasby@pervasive.com> 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 jnasby@pervasive.com > 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 >
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) | fullermd@over-yonder.net
Systems/Network Administrator | http://www.over-yonder.net/~fullermd/
On the Internet, nobody can hear you scream.
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.
Kevin Murphy <murphy@genome.chop.edu> 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
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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.
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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.
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
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
At 8:57 AM -0500 11/1/05, Tom Lane wrote:
>Kevin Murphy <murphy@genome.chop.edu> 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
dan@sidhe.org have teddy bears and even
teddy bears get drunk
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
On Tue, Nov 01, 2005 at 11:31:36PM +0200, Yonatan Ben-Nes wrote: > 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 <snip> Note: Just because your coding style uses prepared statements doesn't mean it uses them in the server. For example, in Perl DBI it has that whole prepare/execute cycle, but the values are actually interpolated (with appropriate quoting) on the client side and sent as a normal query. Hence have your cake and eat it too... Now, in theory a newer DBI version could use server side prepared statements but I imagine I'll be turning them off. I don't have any queries with a significant parse/plan cost to make it worth the costs you mention here... It's a pity the protocol doesn't have a single shot prepare/bind command which would allow you to send the values out-of-line (no quoting issues) but still provide them at the planning/optimising stage to get good plans. Ah well, can't have everything. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Tue, Nov 01, 2005 at 11:31:36PM +0200, Yonatan Ben-Nes wrote: > Jim C. Nasby wrote: > >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. So don't maintain them. Just re-plan every time. My point is using bound parameters to protect against injection. As for the sub-optimal planning, I don't think that's normally an issue unless your data distribution is uneven. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, Nov 01, 2005 at 11:00:26PM +0100, Martijn van Oosterhout wrote: > It's a pity the protocol doesn't have a single shot prepare/bind > command which would allow you to send the values out-of-line (no > quoting issues) but still provide them at the planning/optimising stage > to get good plans. Ah well, can't have everything. Is there any reason that couldn't be added? Sounds like it might make a good newbie TODO... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Am Dienstag, den 01.11.2005, 23:31 +0200 schrieb Yonatan Ben-Nes: > 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. You could just wrap the creation and execution of your SQL in your database abstraction layer. Where execute() prepares and then executes and executemany() prepares and then runs an array of data to execute.
On Tue, Nov 01, 2005 at 08:57:04AM -0500 I heard the voice of Tom Lane, and lo! it spake thus: > > 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 trick is to make it a religious ritual. I escape things into _q variables: $name = $_REQUEST['name']; $name_q = db_quote($name); And have myself thoroughly trained to ONLY use _q variables in building queries. Of course, once in a while, I forget to _create_ the _q version before using it, but then I get a nice loud error message castigating me for it. I often (not consistently) create _q variables even for known-good strings and such that I hardcode into the program. It could well be that using prepared statements is by various metrics a "better" way to go about things. But I'm far too lazy to try and reprogram my fingers ;-) -- Matthew Fuller (MF4839) | fullermd@over-yonder.net Systems/Network Administrator | http://www.over-yonder.net/~fullermd/ On the Internet, nobody can hear you scream.
My point is that with magic_quotes on in PHP, php already escapes quotes for you in all inbound variables. This makes the process automatic, and therefore fool proof, which is kinda the whole point. You want a mechanism that there isn't an easy way around, like forgetting to db_quote once in a while. I'm just trying to find out if there is an example where magic quotes by itself doesn't work, and there is a viable injection attack possible, and if so, what it is, so I can figure out how to prevent it ;). Alex. On 11/1/05, Matthew D. Fuller <fullermd@over-yonder.net> wrote: > On Tue, Nov 01, 2005 at 08:57:04AM -0500 I heard the voice of > Tom Lane, and lo! it spake thus: > > > > 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 trick is to make it a religious ritual. I escape things into _q > variables: > > $name = $_REQUEST['name']; > $name_q = db_quote($name); > > And have myself thoroughly trained to ONLY use _q variables in > building queries. Of course, once in a while, I forget to _create_ > the _q version before using it, but then I get a nice loud error > message castigating me for it. I often (not consistently) create _q > variables even for known-good strings and such that I hardcode into > the program. > > It could well be that using prepared statements is by various metrics > a "better" way to go about things. But I'm far too lazy to try and > reprogram my fingers ;-) > > > -- > Matthew Fuller (MF4839) | fullermd@over-yonder.net > 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 >
On Nov 3, 2005, at 4:26 , Alex Turner wrote: > My point is that with magic_quotes on in PHP, php already escapes > quotes for you in all inbound variables. This makes the process > automatic, and therefore fool proof, which is kinda the whole point. > You want a mechanism that there isn't an easy way around, like > forgetting to db_quote once in a while. I'm just trying to find out > if there is an example where magic quotes by itself doesn't work, and > there is a viable injection attack possible, and if so, what it is, so > I can figure out how to prevent it ;). I'm wondering if using magic_quotes will have issues down the pipe when backslash escaping is no longer the default in PostgreSQL to follow SQL spec. Am I correct in thinking that either the SQL statements would have to be rewritten to use E'' strings, the server setting would have to allow the use of backslashes, or magic_quotes would have to be turned off and variables otherwise escaped to prevent SQL injection? As an aside, it's interesting to see that the PHP documentation states: --- Magic Quotes is a process that automagically escapes incoming data to the PHP script. It's preferred to code with magic quotes off and to instead escape the data at runtime, as needed. --- http://jp.php.net/magic_quotes Michael Glaesemann grzm myrealbox com
On Nov 2, 2005, at 6:08 PM, Michael Glaesemann wrote: > As an aside, it's interesting to see that the PHP documentation states: > --- > Magic Quotes is a process that automagically escapes incoming data to > the PHP script. It's preferred to code with magic quotes off and to > instead escape the data at runtime, as needed. Haven't been totally immersed in this thread but here are reasons given for not using Magic Quotes: http://us2.php.net/manual/en/security.magicquotes.whynot.php And here is pg_escape_string() : http://us3.php.net/manual/en/function.pg-escape-string.php
Curiously none are security reasons, they are more portability reasons (and pretty thin ones at that)... but then this is PHP we are talking about - let me just say register_globals and end it there. I would have to say that for security purposes - I would want magic quotes _on_ rather than off for the whole reasons of SQL Injection that we already talked about. Generally most scripts I write spend more time sending data to the DB then re-reading straight from the DB rather than re-using data pushed through POST/GET, and the functions that are exceptions auto unescape the data again for me... Alex On 11/2/05, Matthew Terenzio <matt@jobsforge.com> wrote: > > On Nov 2, 2005, at 6:08 PM, Michael Glaesemann wrote: > > > As an aside, it's interesting to see that the PHP documentation states: > > --- > > Magic Quotes is a process that automagically escapes incoming data to > > the PHP script. It's preferred to code with magic quotes off and to > > instead escape the data at runtime, as needed. > Haven't been totally immersed in this thread but here are reasons given > for not using Magic Quotes: > > http://us2.php.net/manual/en/security.magicquotes.whynot.php > > And here is pg_escape_string() : > > http://us3.php.net/manual/en/function.pg-escape-string.php > >
On 03.11.2005 04:12, Alex Turner wrote: > I would have to say that for security purposes - I would want magic > quotes _on_ rather than off for the whole reasons of SQL Injection > that we already talked about. magic_quotes is evil and does if anything only prevent the simplest cases of SQL injections. Keep it turned off. Use http://php.net/pg_query_params exclusively to build secure queries.. -- Regards, Hannes Dorbath
Hannes Dorbath wrote: > On 03.11.2005 04:12, Alex Turner wrote: > >> I would have to say that for security purposes - I would want magic >> quotes _on_ rather than off for the whole reasons of SQL Injection >> that we already talked about. > > > magic_quotes is evil and does if anything only prevent the simplest > cases of SQL injections. Keep it turned off. Use > http://php.net/pg_query_params exclusively to build secure queries.. > > The problem with pg_query_params is that you will be forced to use an RC version of PHP.... I don't know about you but I think that for production sites I prefer to use the final versions. I think that prepared statements is the best solution here even if its encumbering everything alittle...
Please, enlighten us all and demostrate a case of SQL Injection that gets around magic quotes. I know am I trying to think of one - and I can't come up with one. Instead of just claiming it to be 'evil' why don't you actualy back the statement up with some reasoned arguments? I hate FUD. Alex On 11/3/05, Hannes Dorbath <light@theendofthetunnel.de> wrote: > On 03.11.2005 04:12, Alex Turner wrote: > > I would have to say that for security purposes - I would want magic > > quotes _on_ rather than off for the whole reasons of SQL Injection > > that we already talked about. > > magic_quotes is evil and does if anything only prevent the simplest > cases of SQL injections. Keep it turned off. Use > http://php.net/pg_query_params exclusively to build secure queries.. > > > -- > Regards, > Hannes Dorbath > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
On 03.11.2005 16:15, Alex Turner wrote:
> Please, enlighten us all and demostrate a case of SQL Injection that
> gets around magic quotes.
Just someone needs to forget to put quotes around a param..
$q = "DELETE FROM foo WHERE bar = {$_GET['id']};";
instead of
$q = "DELETE FROM foo WHERE bar = '{$_GET['id']}';";
It deadly, with or without magic_quotes. pg_query_params() prevents such
situations. Besides some encoding tricks to encode single quotes
(%2527).. mb_string, mod_rewrite there are many things that /can/
prevent MQ from working as expected, search google. And even if MQ does
what it should, errors like the above are very common. Oh, and why MQ is
evil, really not all data passed to a PHP script is meant to be used as
query params, and if so, create a recursive version of array_map() and
use that with pg_escape_string as callback.
--
Regards,
Hannes Dorbath
Prepared statements are the way to go.
I developed my own prepared statements methodology (I called it "SafeQuery")
some time back before discovering that others had done it. It's so nice,
since I've not worried about SQL injection for YEARS.
Sample of my API:
<?
$sql="SELECT auth.login
FROM auth
WHERE username='[username]'
AND password='[password]'
";
$todb=array('username'=>$_REQUEST['username'],
'password'=>$_REQUEST['password']);
if (!$res=$MDB->SafeQuery($sql, $todb))
return Error("Database query failure");
?>
SafeQuery checks:
1) That the variables in the query (in brackets) and in the input array all
match up.
2) Runs pg_escape_string on all elements in $todb;
3) Copy/Pastes strings from the array into the query.
4) Runs query against DB, returns results from pg_exec();
-Ben
On Tuesday 01 November 2005 05:27, Kevin Murphy wrote:
> 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
>
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978