Page 1 of 1
Sql injection
Posted: Thu Dec 29, 2005 2:01 am
by IAD
Hey,
Php is not new to me [ i'm working with it for 1 + years ], and i know that Sql Injection problem are not rare, anyway, today i'm blocking any $_GET, COOKIES, And htmlspecialchars();
Anyway, i wan't to know, if theres another way to protect my queries, from injection tryouts.
Thanks in advance, Tal.
Posted: Thu Dec 29, 2005 3:01 am
by RobertGonzalez
Validating user entries is a good start. If you know that your script is supposed to be looking for a particular type of data, validate for it (dates, for example, or phone numbers or city names, etc). Sometimes I even go so far as to flush out the characters that I know might cause my queries problems, such as "=", "where", "1 = 1", etc. Knowing what your script does and what you want people to be able/not be able to access is a nice starting point.
Posted: Thu Dec 29, 2005 3:53 am
by Jenk
Posted: Thu Dec 29, 2005 10:15 am
by josh
if you use mysql_real_escape_string *any* data is 100% safe to send to a query in terms of SQL injection not happening
htmlentities() should be used when you are displaying data back to the user, use it on *every* variable that you are displaying, unless that variable was validated by a regex..
which leads me to my next point, use regex everywhere you can, if your form is supposed to accept a 10 digit phone number and just that, mysql_real_escape_string will still stop sql injection but what if some other part of your application *depends* on that data being a 10 digit numeric string. This is the thing most developers overlook when they first start out. It is also much more user friendly for the user to get an error telling them their phone number got entered wrong then to see their phone number show up as a"0" because mysql converted it to an int wrong.
PS - you need to use mysql_real_escape_string() before data goes into any kind of query regardless if it came from the user, a cookie, an http request, another query, and xml feed, etc... it does not matter.
Posted: Thu Dec 29, 2005 5:26 pm
by Jeroen Oosterlaar
As a basic rule of thumb: always validate any variable that can be manipulated by user input, such as form data, cookies en URL parameters and that is being used within instructive statements such as SQL queries. When you make sure that such data are controlled for, there is little change that your application is prone to injection.
Posted: Tue Jan 03, 2006 6:24 am
by Maugrim_The_Reaper
I received a letter today from the "Other Databases" to point out there exists database specific escaping functions for many (but not all databases). Since I'm a member of the PostgreSQL fanclub just thought I'd mention it...
Filter-Validate-Escape.
If a piece of user input is supposed to be an integer, then check it really is an integer. The trick here (and much disputed by many) is that once you filter/validate user input you should remember to escape for any SQL (via mysql_real_escape_string() or for Postgres pg_escape_string()) or escape for HTML (via htmlentities()). Even if it seems to make no sense.
So even if you know its an integer (from filtering/validating) - still escape. One day your filter logic may fail and you will realise why this is a good if strangely obsessive practice...

Posted: Tue Jan 03, 2006 6:30 am
by timvw
As soon as your database driver supports it, i would suggest to switch to prepared statements and then use parameter binding (allows you to stop thinking about *_(real_)escape stuff...
Posted: Tue Jan 03, 2006 7:20 am
by Maugrim_The_Reaper
MySQL5...?
Posted: Tue Jan 03, 2006 7:39 am
by Roja
timvw wrote:As soon as your database driver supports it, i would suggest to switch to prepared statements and then use parameter binding (allows you to stop thinking about *_(real_)escape stuff...
With the bliss of Adodb powering my apps, I've never known the pain. It has supported bind variables on all version of mysql and postgres for years now (yes, including mysql-3.23).
Adodb 4tw!
Posted: Tue Jan 03, 2006 9:54 am
by Maugrim_The_Reaper
It'll be nicer when it decides to support the native pg_escape_string() function call...
As far as I know (or maybe knew - past tense being more accurate) ADOdb does not however support Prepared statements in the real sense of pre-compilation. Not all that surprising given you would need MySQL 5, and the drivers are more in line with mysql/mysqli than anything else. There is a Prepare method but its there primarily for compat with the DBMS's with Prepare statements already included for years...
The current binding statements it allows are however a truly cool inclusion. Just bind, and the escaping (not for Postgres just yet I think) is done transparently... No need to even consider SQL data escaping (unless you're using Postgres maybe).
Sorry, have a sore thumb since I noticed pg_escape_string() was missing a while back as Roja can probably testify

Posted: Tue Jan 03, 2006 9:58 am
by Roja
Maugrim_The_Reaper wrote:It'll be nicer when it decides to support the native pg_escape_string() function call...
Agreed!
Maugrim_The_Reaper wrote:As far as I know (or maybe knew - past tense being more accurate) ADOdb does not however support Prepared statements in the real sense of pre-compilation.
Not true. It does on the platforms that support it, like Oracle. On the ones that don't, it does the escaping, but emulates the prepare portion.
Maugrim_The_Reaper wrote:Not all that surprising given you would need MySQL 5, and the drivers are more in line with mysql/mysqli than anything else. There is a Prepare method but its there primarily for compat with the DBMS's with Prepare statements already included for years...
Exactly right. Oddly, I do find that prepare (perhaps because its usually in a transaction) does gain me a performance gain on PGSQL. Shrug. I mostly like it for the hassle-free escaping.
Maugrim_The_Reaper wrote:The current binding statements it allows are however a truly cool inclusion. Just bind, and the escaping (not for Postgres just yet I think) is done transparently... No need to even consider SQL data escaping (unless you're using Postgres maybe).
Sorry, have a sore thumb since I noticed pg_escape_string() was missing a while back as Roja can probably testify

Dead on the money. Here's to hoping he slips the patch into the next version. imho, the last few versions, he's been dropping patches more often. Dunno why, perhaps he's over busy. Certainly not unique in the php world these days.

Posted: Tue Jan 03, 2006 10:37 am
by Maugrim_The_Reaper
I posted the request a while back, so maybe it'll hit a changelog near phpLens someday soon
As far as I know (or maybe knew - past tense being more accurate) ADOdb does not however support Prepared statements in the real sense of pre-compilation.
Meant that in relation to MySQL - you're too literal, I may have to actually proof read my posts from now on...

The ADOdb Prepare() method just returns the SQL string I think. My mind is recently focused more on ADOdb-Lite (a case of looking under the bonnet and getting interested by it - I'm easily amused when too long idle) so its anyone's guess how reliable my ADOdb internals knowledge is.
Transactions... Ah, I'm beginning to remember vaguely why I prefer Postgres. Hey, fair play to MySQL dropping MyISAM as the default SE. They're catching up...kind of. Maybe by MySQL 7 they'll figure out how to add everything Pg added last century...

Any day now PHP5 with PostgreSQL 8.1 will be standard on all shared hosting servers...
Posted: Tue Jan 03, 2006 11:59 am
by Roja
Maugrim_The_Reaper wrote:Meant that in relation to MySQL
Ah. My bad, sorry.
Maugrim_The_Reaper wrote:The ADOdb Prepare() method just returns the SQL string I think.
I haven't looked under the hood (bonnet!), but after quoting, yes, I think thats accurate for mysql, until v5.
Maugrim_The_Reaper wrote:My mind is recently focused more on ADOdb-Lite (a case of looking under the bonnet and getting interested by it - I'm easily amused when too long idle) so its anyone's guess how reliable my ADOdb internals knowledge is.
I'm deeply interested in it myself, and may end up switching at some point. There are just a few key items that I really need for it to support and I could switch. Maybe PJ will merge them at some point, and I can join the club. Til then, I'm adodb-loyal.
Maugrim_The_Reaper wrote:Transactions... Ah, I'm beginning to remember vaguely why I prefer Postgres.
Yeah, its really rather sexy to have native support for transactions and not have to worry about table types and support.
Maugrim_The_Reaper wrote:Hey, fair play to MySQL dropping MyISAM as the default SE. They're catching up...kind of. Maybe by MySQL 7 they'll figure out how to add everything Pg added last century...

Any day now PHP5 with PostgreSQL 8.1 will be standard on all shared hosting servers...
I agree. Mysql5 is in my opinion *extremely* competitive with Postgres. If you ignore the license, the difference between the two is so minor that it will totally depend on your needs which is better.
Until mysql5 and php5 are the default on most webhosts.. I'll still be complaining about mysql's shortcomings.