Making the Switch :: Magic Quotes Off

Sayian

Ok, so far ive relied on magic quotes GPC to "Escape" my data ..

Ive read at least 10 tutorials on this, but i still do not understand what is happening.

Ive read that, they need to be turned off, and my data escaped manually.

The only time this data needs to be escaped is when im inserting it into the database is this correct?

Ok, so what about if im pulling data from the database? I must use stripslashes?

Hmmm .. it seems right now that all the data in my database doesnt contain slashes .. i have private messages, names of things, ect. All with no slashes in the values, but magic quotes is on. Why is this?

I might be getting this all wrong, but help me to understand how this works, and what i need to do to be safe from SQL injection.

Thank you.

MarkR

Sayian wrote:
Ive read that, they need to be turned off, and my data escaped manually.

While magic_quotes_gpc is on, it will automatically addslashes() to anything coming in, regardless of whether you're going to put it in the database or not.

For example, if something goes into a hidden field in a form, then is passed through to another page, it would then have addslashes() called TWICE on it, which would be bad.

The only time this data needs to be escaped is when im inserting it into the database is this correct?

Yes. Or at least, that's the only time it needs to be escaped to prevent SQL injections. There are other threats which need different types of escaping.

Ok, so what about if im pulling data from the database? I must use stripslashes?

No. The slashes don't go into the database (unless of course, there are slashes genuinely in the data). The slashes just escape other characters (quotes and other slashes) on the way in. The DB doesn't actually store them, they're part of its syntax.

Hmmm .. it seems right now that all the data in my database doesnt contain slashes .. i have private messages, names of things, ect. All with no slashes in the values, but magic quotes is on. Why is this?

Because the database interprets them in its syntax, not as part of the data.

Mark

Drakla

Also take a look at this thread and the advice that Sxooter gives, especially picking the right tool for the right job.

http://phpbuilder.com/board/showthread.php?t=10316334

This article also sums up a lot of the hair tearing
http://www.webmasterstop.com/63.html

Sayian

Ok, is it possible to use mysql_real_escape_string with magic quotes ON? or do they have to be off to use this?

Drakla

You can use real escape as long as you've got a db connection. If magic quotes is on stripslashes the data first, then use mysql_real_escape_string on it. That might seem ludicrous, but it's actually very good practise.

And just to iterate again for anybody out there still not listening:

YOU NEVER NEED TO STRIPSLASH data you pull from a db unless you've been stupid enough to escape already escaped data

Sxooter

Sayian wrote:
The only time this data needs to be escaped is when im inserting it into the database is this correct?

Close, but not quite. ANYTIME you send a query to your database you need to make sure your data are properly escaped.

If you have a query like:

select * from sometable where somefield like '%$userinputhere%';

and someone replaced $userinput with the correct string sequence, they may be able to run ANY arbitrary query they want to, including one that deletes things in tables or drops whole tables and databases, if the account accessing the db has enough power.

csn

Drakla wrote:
You can use real escape as long as you've got a db connection. If magic quotes is on stripslashes the data first, then use mysql_real_escape_string on it. That might seem ludicrous, but it's actually very good practise.

Um, how so?

csn

Drakla wrote:
You can use real escape as long as you've got a db connection. If magic quotes is on stripslashes the data first, then use mysql_real_escape_string on it. That might seem ludicrous, but it's actually very good practise.

Um, how so?

frost110

Wow this is awesome information. I just found out I have been doing my whole site...WRONG! sucks ass. But thanks for the information I plan on making a fix. It will take a long while but man thanks!

--FrosT

Sxooter

because mysql_real_escape_string uses the char set of the currnetly connected database, so its escaping is char set sensitive.

csn

Does that matter though? I used just magic quotes a really long time ago and never had any problems. The back slash is probably a pretty safe character across char sets, well at least in the western world, not that most developers use more than one or two different char sets.

Sxooter

Did you read the links in the other thread? add_slashes will not protect you from a determined person. It is defeatable. And the sample code to do so is in the wild. add_slashes will likely never be "fixed".

If an exploit for the mysql_real_xxx function, then mysql ab can issue a bug fix, since this part of the api is used by ALL database applications that talk to mysql.

Sayian

After this thread and reading in the manual about SQL injections i decided to turn magicquotes GPC off, and start using mysql_real_escape_string.

Not only do i feel more safe, lol, but for some reason my site appears to have increased in speed some, not sure if this is an after effect.

Thanks for the info guys!

csn

Ah, I stand enlightened. 🙂 I always used adodb, or pg_escape_string(). Andi Gutmans's post about using a library is probably good advice. Since switching to Rails, it's like I don't even have to think much about the database.

Sayian

How is rails? ... ive looked at it

csn

Rails and Ruby are awesome. I'm much more productive, and happy. 🙂 I haven't found a single reason to go back to PHP. In fact, I'm converting all my PHP stuff to Ruby and Rails.

Ruby is easy to pick up. Rails has a much bigger learning curve, but it's a nice, friendly, newbie-to-expert on-track one.