MySQL Quote Problems

[bejitto101]

Hey there. Having some problems with part of my site im updating. Basically, im just trying to update my database with user entered data. One of the data fields calls for a description and sometimes theres single quotes and double quotes. I try to update my database with the following code:


$query = "UPDATE Courses_1 SET cid='$cnum', name='$cname', descrip='$desc', category='$caty' WHERE cid='$cid'";


If there are single quotes or double quotes in the description, it kills the query. Any good simple way to fix this? Or do I just have to filter the quotes out when I insert the data and add them back in when I pull the data from the db?

[Maximus9999]

oops, misunderstood

[ellisgl]

mysql_real_escape_string or mysql_escape_string on all your vars. If those are not available - then use addslashes

You'll have to use stripslashes when you pull the data out.

and also
$query = "UPDATE `Courses_1` SET `cid`='$cnum', `name`='$cname', `descrip`='$desc', `category`='$caty' WHERE `cid`='$cid'";

[NogDog]

...
You'll have to use stripslashes when you pull the data out.
...
Actually, no you won't. The mysql_real_escape_string(), etc. functions escape certain characters with a back-slash so that the string can be used in an SQL statement. But those back-slashes do not actually make it into the database records. It's the same thing as using back-slashes in a PHP echo statement for internal quotes:

echo "This is <a href=\"test.php\">a test</a>.";

The back-slashes here are used to tell the parser those are literal quote characters withing the string literal, not markers for the start or end of a string literal. They do not appear in the actual HTML output.

The same thing happens in SQL: the back-slashes tell the SQL parser to treat the character that follows it as its literal value, but the back-slash itself is not part of the actual data that gets stored, compared, or whatever is being done (unless, of course, you have a double back-slash indicating a literal back-slash character).

[ellisgl]

Ah.. Thanks for correcting me on that Nog.

[NogDog]

One thing that causes confusion, though, is if magic_quotes_gpc is enabled. If it is, and you run form data through mysql_real_escape_string() without first stripping the slashes that the magic_quotes_gpc option added, then you would end up escaping those back-slashes, making you think that the escape function added them to the data.

[ellisgl]

so you would just do a test for magic_quotes_gpc and if it's on - strip the slashes then put it thru the mysql_real_escape_string - right?

[NogDog]

so you would just do a test for magic_quotes_gpc and if it's on - strip the slashes then put it thru the mysql_real_escape_string - right?
Yep. :)

[bejitto101]

Thanks for the help! Another question tho. I was looking at the php.net site about mysql_real_escape_string. In there they have an example.
Should I format my code like this:

$query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)", mysql_real_escape_string($product_name, $link), mysql_real_escape_string($product_description, $link), $_POST['user_id']);

Or can I just format it like this for all my variables:

$product_description=mysql_real_escape_string($product_description);

Thanks

[NogDog]

You can do whichever you find easiest to read and maintain. I like to do something like:

function sanitize($value)
{
if(is_numeric($value))
{
return($value); // it's a number, so no sanitizing is needed
}
if(get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
// escape SQL special chars and quote string:
return("'" . mysql_real_escape_string($value) . "'");
}

$query = sprintf(
"SELECT * FROM table WHERE id = %d AND category = %s",
sanitize($_POST['id']),
sanitize($_POST['category'])
);

[bejitto101]

Sorry for all the questions, but I have yet another. I am not to familiar with sprintf and printf. Whats are the advantages of using them?

[NogDog]

Sorry for all the questions, but I have yet another. I am not to familiar with sprintf and printf. Whats are the advantages of using them?
Functionally, they have no special advantage, but they provide one method to organize your code. I find it easier to to use them with complex outputs rather than lots of string concatenation and/or variable assignments; but I suppose to some degree it's just a matter of style.

Which do you like better?...

// this...
$query = "INSERT INTO table_name (id, fname, lname, password) VALUES (" .
(int)$_POST['id'] . ",'" . mysql_real_escape_string($_POST['fname']) . "','" .
mysql_real_escape_string($_POST['lname']) . "','" . md5($_POST['password']) ."')";

// ...or this?
$query = sprintf(
"INSERT INTO table_name (id, fname, lname, password) " .
"VALUES(%d, '%s', '%s', '%s')",
(int)$_POST['id'],
mysql_real_escape_string($_POST['fname']),
mysql_real_escape_string($_POST['lname']),
md5($_POST['password'])
);

[ellisgl]

I find that some of the code I've gone through that people will use sprintf and printf in the strangest ways. Like printf with a mysql_query... instead of just making a var then query the var.

Of course (some what off subject) - storing all the pages of a site in a session. (Some explain why anyone would want to do that?)