[PHP] SQL Injection

Dear List -

I am aware of a long email trail on this subject, but there does not
seem to be a resolution.

Is it possible to have a "meeting of the minds" to come up with (an)
appropriate method(s)?

Thanks.

Ethan Rosenberg



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

On Fri, Jun 8, 2012 at 12:37 PM, Ethan Rosenberg <[email protected]> wrote:
> Is it possible to have a "meeting of the minds" to come up with (an)
> appropriate method(s)?

Minds, meet prepared statements :)

Adam

--
Nephtali:  A simple, flexible, fast, and security-focused PHP framework
http://nephtaliproject.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

-----Original Message-----
From: Adam Richardson [mailto:[email protected]]
Sent: Friday, June 08, 2012 11:50 AM
To: PHP-General
Subject: Re: [PHP] SQL Injection

On Fri, Jun 8, 2012 at 12:37 PM, Ethan Rosenberg <[email protected]>
wrote:
> Is it possible to have a "meeting of the minds" to come up with (an)
> appropriate method(s)?

Minds, meet prepared statements :)

Adam

--
Nephtali:  A simple, flexible, fast, and security-focused PHP framework
http://nephtaliproject.com

--
PHP General Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php


PDO is the way to go :D

Jen




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

>> Is it possible to have a "meeting of the minds" to come up with (an)
>> appropriate method(s)?


> Minds, meet prepared statements :)


> PDO is the way to go :D


Not to refute the above advice one bit (not to mention oppose the arguments against escaping in general) ... but just curious - can anyone demo a hack that effectively injects past mysqli_real_escape_string(), while using utf-8 ? It may just be a matter of time (or already?) before mysqli_real_escape_string is *proven* ineffective (w/utf-8) ... but here I am just attempting to gather facts.

Thanks
-Govinda


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

On 06/08/2012 10:31 AM, Govinda wrote:
>>> Is it possible to have a "meeting of the minds" to come up with (an)
>>> appropriate method(s)?
>
>
>> Minds, meet prepared statements :)
>
>
>> PDO is the way to go :D
>
>
> Not to refute the above advice one bit (not to mention oppose the arguments against escaping in general) ... but just curious - can anyone demo a hack that effectively injects past mysqli_real_escape_string(), while using utf-8 ? It may just be a matter of time (or already?) before mysqli_real_escape_string is *proven* ineffective (w/utf-8) ... but here I am just attempting to gather facts.
>
> Thanks
> -Govinda
>
>

Ah, but what if I use sqlite or postgres?

IMHO, the discussion needs to be a the best way to prevent SQL injection
across all possible DB types. Not just mysql.

--
Jim Lucas

http://www.cmsws.com/
http://www.cmsws.com/examples/
http://www.bendsource.com/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Jim Lucas wrote:
>> Not to refute the above advice one bit (not to mention oppose the arguments
>> against escaping in general) ... but just curious - can anyone demo a hack
>> that effectively injects past mysqli_real_escape_string(), while using utf-8
>> ? It may just be a matter of time (or already?) before
>> mysqli_real_escape_string is *proven* ineffective (w/utf-8) ... but here I am
>> just attempting to gather facts.

> Ah, but what if I use sqlite or postgres?

Or Firebird ;)

> IMHO, the discussion needs to be a the best way to prevent SQL injection across
> all possible DB types. Not just mysql.

The main thing to avoid is building queries from elements that are directly
loaded from the form inputs. While it is difficult to build sort elements for
queries that use parameters, having a mechanism like ADOdb's datadict where one
can filter SQL based on the identified field names does make life easier.

While the problems of dealing with student names such as 'Delete from student'
are easily solved by only using them in parameter arrays.

A few simple basics cover the vast majority of traditional SQL injection problems?

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

>> Ah, but what if I use sqlite or postgres?
>
> Or Firebird ;)

good point.


>> IMHO, the discussion needs to be a the best way to prevent SQL injection across
>> all possible DB types. Not just mysql.
>
> The main thing to avoid is building queries from elements that are directly loaded from the form inputs. While it is difficult to build sort elements for queries that use parameters, having a mechanism like ADOdb's datadict where one can filter SQL based on the identified field names does make life easier.
>
> While the problems of dealing with student names such as 'Delete from student' are easily solved by only using them in parameter arrays.
>
> A few simple basics cover the vast majority of traditional SQL injection problems?

Yes, apparently.

Part of why I even asked is to get a sense of the shelf life on legacy code (that relies on escaping) which I am not keen to have to re-write, for free, until I really must.


-Govinda
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Govinda <[email protected]> wrote:

>>> Ah, but what if I use sqlite or postgres?
>>
>> Or Firebird ;)
>
>good point.
>
>
>>> IMHO, the discussion needs to be a the best way to prevent SQL
>injection across
>>> all possible DB types. Not just mysql.
>>
>> The main thing to avoid is building queries from elements that are
>directly loaded from the form inputs. While it is difficult to build
>sort elements for queries that use parameters, having a mechanism like
>ADOdb's datadict where one can filter SQL based on the identified field
>names does make life easier.
>>
>> While the problems of dealing with student names such as 'Delete from
>student' are easily solved by only using them in parameter arrays.
>>
>> A few simple basics cover the vast majority of traditional SQL
>injection problems?
>
>Yes, apparently.
>
>Part of why I even asked is to get a sense of the shelf life on legacy
>code (that relies on escaping) which I am not keen to have to re-write,
>for free, until I really must.
>
>
>-Govinda
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php

I think you can happily sanitise data where it makes sense, and use bound parameters elsewise. So when you expect a number, its easy to check for and force a sensible default. Likewise for things like dates, or names of articles (probably a popular need with a CMS) you can check and enforce particular characters.

Outside of that, without bound params you run a potential risk (even if only slight). You can do stuff like base64 encode values, but then you lose a lot of the ability to search through your DB after.

Thanks,
Ash
http://ashleysheridan.co.uk

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

> I think you can happily sanitise data where it makes sense, and use bound parameters elsewise. So when you expect a number, its easy to check for and force a sensible default. Likewise for things like dates, or names of articles (probably a popular need with a CMS) you can check and enforce particular characters.
>
> Outside of that, without bound params you run a potential risk (even if only slight). You can do stuff like base64 encode values, but then you lose a lot of the ability to search through your DB after.


What would you say in the case of having used CodeIgniter (w/it's modified 'Active Record Class', before PDO was an (easy/built-in) option in CodeIgniter) to develop an app that serves content in dozen(s) of languages through a custom international CMS... and now they want "a search box" so end users can search all the pages (db data) of the "site" for that country (in that country's main language)? IOW form input that I cannot just force/sanitize to e.g. (english) alphanumeric (+ spaces), and I cannot just switch to using PDO without rewriting all the code in all the model files.

Thanks
-Govinda
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php