I have read tutorials on mysql_real_escape_string, php filters, addslashes(), stripslashes(), etc. and still considering which combination to use and if its the right way of doing it.

Currently i am testing/using the db connection class from a tutorial found here in D.I.C (link) which I have yet to figure out how to modify properly to incorporate mysql_real_escape_string() into it since i would need a connection to db before i can use mysql_real_escape_string(). But is mysql_real_escape_string() sufficient?

As for php filters, while i like the idea of it but it seems tedious especially if i have a lot of data to filter. I know most of the work and validation can be done with javascript to help prevent this but i always make sure i have a fall back should javasript be turned off.

So yeah... I was wondering how you guys handle SQL injections in your php applications?



cheers...

Well, I used to use mysql_real_escape_string, but as it's been pointed out to me there's a lot of room for human error (forgetting to escape your strings before putting them in), but it's probably the best way for you to escape strings if you're using PHP's mysql_~ functions and you have that function. add slashes and strip slashes are functions mostly for people without that function...

However, I use ADOdb, my statements look like:

Which is supposed to be better because it's supposed to remove room for human error. It escapes $id and plugs it into the ? in the query.

Yours,
Shane~

This post has been edited by ShaneK: 10 Jun, 2009 - 04:55 AM

I've said it before and I'll say it again: PREPARED STATEMENTS. Geez, I should really write a tutorial or something so that I don't have to keep repeating myself.

Unless you have a really crappy web host who doesn't support PHP5 (in which case you should just get a better host), you should be using the PDO or MySQLi extensions for database access. Both of those support something called "prepared statements", which are essentially parameterized SQL queries. Rather than inserting your data directly into the SQL string, you insert place holders and then "prepare" the query. You then execute the query in a separate step, at which point the actual data is passed in. The preparation actually pre-compiles the SQL query, so that the data can't modify the structure of the statement - it's just treated as plain data, not part of the query text.

As ShaneK mentioned, there are also libraries like ADOdb that support parameterized queries that emulate prepared statements and give you the same benefit of taking the manual work out of escaping data. Personally, I prefer to use PDO because it's a standard extension, but these types of libraries are fine too. The main point is that these days you absolutely should not be using a data access layer that requires you to escape your query data by hand.

Also:

For the record, nothing that involves security or data integrity should ever be done in JavaScript. Or, more precisely, you can do it in JavaScript, but you still have to do it on the server-side too. Server-side validation is never optional. It's not just about users having JavaScript disabled - any malicious user with a copy of cURL can just POST any old data they please directly to your page, completely bypassing all client-side code. Never rely on anything that comes from the client being safe.

AdaHacker, maybe you should do the tutorial if you have the time. I definitely would have a read and bookmark it under useful resources.


Couldn't have agreed more with you. Kinda the reason for starting the topic to find out what and how you guys tackle the issue and how i should approach this since i've yet to put together a solid foundation to overcome the problem. Thus the reason i'm still researching on stuff while picking up PHP again, had to drop it for almost 2 years due to work requirements.

Never the less thanks ShaneK & AdaHacker for sharing your thoughts on the topic. Will do my research on PDO and ADOdb later this evening after work.

ps. thanks for moving the topic to the right forum.

This post has been edited by saturnx: 10 Jun, 2009 - 05:09 PM

This was helpfull for me too!
thank you!

Not quite the same thing but I'm a big fan of Doctrine

This is also an interesting snippit on sanitize database input, though all things considered its really just an introduction - his code could use many improvements, and it doesn't really account for all that much.



AddHacker's totally right - you can pretty much circumvent any client-side code on most websites with a jQuery bookmarklet and Firebug... or even just your address bar and javascript(), really.

This is so helpful, i didnt know these methods existed im new to php.

I was using a function that emulates .contains in vb.net to find potential sql hacks and remove them from the string before using it to query the database.

Hey there,

I recently came up with a neat trick. Or at least I thought it was neat.

I have a database class than handles all MySQL queries. So on top of using the many functions to ensure data is safe I also have this code:



Obviously, its easy to get around this with a simple WHERE 1=1 but its just an added level of protection!

This post has been edited by Lang14: 15 Aug, 2009 - 01:42 PM

True, but if the developer has the time, it could be done in both as to allow for instant verification, since PHP is unable to do that.

Excellent topic for discussion here, featured so others will get involved

Here is a very good tut on php filters and how to use them
http://www.webreference.com/programming/php/DataFiltering/

His little insight about cutting out CSS, HTML, and Javascript is kind of silly imo. If he wanted to get rid of all those things, leaving just plain text, he might as well just do a simple preg_match with a regexr statement that allows only letters, numbers, and punctuation.

Basic principles are covered at: http://beta.developercenter.org/article/ph...tion-practices/

An article on prepared statements is already planned.

That is something many people don't understand.

Just because your website is developed on the fly does not mean it cannot be saved and modified.

His method although silly, is effective and very dangerous. The only Javascript verification I use, is determining if passwords match, emails match and username availability. I also check data length for required fields.

If a user is willing to type in some crazy data, then they deserve to endure a post back telling them they need to try again!

I use mysql_real_escape_string() to strip harmful characters, then when the time comes that I need to output data to the screen, I use stripslashes() on the strings to return them to their normal state.

You shouldn't need to use stripslashes() when you retrieve the data unless your server is configured incorrectly (to use magic_quotes or the like..).

I'm pretty sure magic quotes are on in the server, but what's that have to do with anything?

When I escape and store a string into the database Hello \"World\", then when I retrieve it into an associative array to print it, it will still be Hello \"World\" won't it? I've always had to strip the slashes before printing it.

No, it won't. That only happens when magic_quotes_runtime is turned on. It automatically escapes any strings that come out of the database. Incidentally, magic_quotes have been deprecated in PHP 5.3 and will be removed from PHP 6, so you probably shouldn't be depending on them.

Believe me, I'm not dependent on them. Their actually a pain in the ass because I go to escape things before input into a database and it ends up escaping them twice (one for magic quotes and another for my mysql_real_escape_string()).