Magic Quotes, mysql_escape etc etc. for newbie!

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
baggypants303
Forum Newbie
Posts: 9
Joined: Sat Apr 01, 2006 11:04 am

Magic Quotes, mysql_escape etc etc. for newbie!

Post by baggypants303 »

Hello everyone,

Firstly I'd like to say I've been a lurker for a whole entire day :) reading up on stuff...and I'm happy to join this forum, it is really unique: no ads??, lots and lots of info, lots and lots of smart people. The first time I visited this forum I actually "shuddered" at the sight of NO ads and thought about how much money the admins here COULD be making, but they don't. Anyhow, moving to my actual question:

I've read up a lot of info on this forum, and other places about magic quotes, mysql_escape_string, mysql_real_escape etc etc. I'm still having trouble understanding this (regardless of whether its been posted or not).

I'm not a new programmer, I've used Java, C, C++ and HTML. But this is my first attack at "dynamic websites", and PHP is a little different...because of security. Now hopefully this thread will also shed some light for others new to databases and the like!

I really do hope this hasn't been explained on the forums anywhere already, or I'll look like a lazy guy who didn't really search well!

So what is magic quotes? I really need someone to break it down to the bare here. Let's say a user inputs this:

(text field input)
Name of book: Catcher in the Rye
Summary of Book: Holden Caulfield's attitude is of a young teenager who never feels the need to grow up!
Author: J.D. Salinger

Notice the apostraphe in Caulfield's. Now the insert statement for that would probably look like:

Code: Select all

$sql = "INSERT INTO books (name, summary, author) VALUES ($_POST['name'], $_POST['summary'], $_POST['author']);"
This would create a problem with the apostraphe...how? Is it because the double quotes in the insert statement are messing with this, therefore it cuts off $_POST['summary'] and chops it. Or is it because MySQL databases cannot handle apostraphes?

Ok, let's say that I know the reason why the apostraphe can't go in by itself. If magic quotes was on it would add a slash before every apostraphe like so: \' would it also add it to \" ? I'm still hazy here, but I'm assuming that when you want to insert code into the database, you want these slashes behinde very quote. However, when displaying it to the browser (or a user) for example in a text input field or as plain HTML...you'd want to strip the slashes? This is where I'm hazy, as the manual uses a escape string even on SELECT statements. I can see why, because someone might be SQL injecting and receiving output such as passwords, etc. But how to attack this problem explicitly? I'd really like someone to explain the few questions just posed here if possible.

So the conclusion is, addslashes() should not be used for VARIOUS reasons already explained in this forum which I won't delve into.
Here is some code from the manual/comments:

Code: Select all

function quote_smart($value) {
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value) || $value[0] == '0') {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}
So with the above code, it can be used for INPUT as well as output? It would look like this?

Code: Select all

$sql = "INSERT INTO books (name, summary, author) VALUES (quote_smart($_POST['name']),quote_smart( $_POST['summary']), quote_smart($_POST['author']));"
Also how do I quote_smart a password (md5) field. Last time I tried it popped no errors (but on my code I couldn't view or edit vice versa the code just stopped functioning?)


Regards,

Baggy.
baggypants303
Forum Newbie
Posts: 9
Joined: Sat Apr 01, 2006 11:04 am

Post by baggypants303 »

Ok, so basically the quotes are a problem since it gets confused with the actual quotes " " in the $sql insert statement...just found that out.

Still not sure what to do when...i.e. outputting data, inputting data into the database. When to escape? Stripslash?
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

You're escaping for SQL (the query language), not the database (the actual storage). When you query the database, none of the escaping you did for the sql is there. The sames applies to strings: $string = 'I\'m writing stuff'; doesn't actually contain the string, but the representation needs the escaping so PHP doesn't think "Oh, the quote is over."

Generally, in memory representations should not be escaped at all until you're about to output it.

So:

POST data needs to be stripslashed if magic_quotes is on
Data from the database generally isn't escaped at all (this is how most things should be)
Any data going into SQL needs to be escaped with mysql_real_escape_string()
Any data being outputted raw in HTML needs to be escaped with htmlspecialchars()

I'm not exactly sure what you're talking about with the md5.
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

htmlspecialchars - or htmlentities('some string of text', ENT_QUOTES, 'UTF-8') ;)
Post Reply