inserting single and double quotes into database

[eMonk]

What's wrong with my mysql_real_escape_string() syntax?

 

$update = "UPDATE model SET name = '$name', height = '" . mysql_real_escape_string($height) . "', hair = '$hair' ";

 

I can't seem to enter in 5'8" into the database for $height.

[imperium2335]

You could addslashes to the variable when putting in it, and when you get it out use stripslashes.

[PFMaBiSmAd]

I can't seem to enter in 5'8" into the database for $height

 

You didn't tell us what problem you are having. Is the value actually inserted into your table when you look directly using your favorite database management tool?

 

If the problem is that you cannot display the value, it is because you need to use htmlentities with the second parameter set to ENT_QUOTES when you output it to the browser so that the quotes in the value don't break the HTML of your web page.

[eMonk]

The problem is the data isn't being inserted properly.

 

When I enter in 5'8" the database shows 5\'

 

I just tried addslashes but samething.

[PFMaBiSmAd]

the database shows

 

Exactly what are you doing to look at the data in the database?

[eMonk]

I'm using phpMyAdmin to look at the data.

[PFMaBiSmAd]

I'm pretty sure that phpmyadmin accurately shows data, even if it contains quotes/escape characters (I use a different database tool and it shows the correct results in the database table when I tried this.)

 

Where is this $height coming from and have you confirmed that the $height variable contains what you expect?

 

This problem isn't being caused by mysql_real_escape_string(). The following code works as expected and inserts and displays the value correctly -

 

<?php
// db connection here...

$height =  <<<EOD
5'8"
EOD;

$query = "INSERT INTO model (height) value ('" . mysql_real_escape_string($height) . "')";
mysql_query($query);

$query = "SELECT height FROM model";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){
echo htmlentities($row['height'],ENT_QUOTES) . '<br />';
}
?>

 

Is your height column a character data type that could hold a string like 5'8"?

[eMonk]

Is your height column a character data type that could hold a string like 5'8"?

 

Ah, that was the problem! I was using varchar(3) thinking I had to enter in height as cm then convert to feet (for single and double quotes) when the database was created. Just changed it to varchar(6).

 

Question about stripslashes....

 

$height = stripslashes(trim($height));

 

displays the data:

 

5'8 (but 5\'8\" is displayed in the table column). Why is the output missing the double quote at the end?

[PFMaBiSmAd]

If the data is being escaped only one time, the \ characters should NOT be in the database.

 

I'm going to guess that php is escaping the form data, due to magic_quotes_gpc and you are escaping it a second time with mysql_real_escape_string.

 

If you can, you need to turn off magic_quotes_gpc. If you cannot turn it off, you can use the get_magic_quotes_gpc function to detect when it is on, and remove the escaping it does before you use mysql_real_escape_string on the data.

 

[eMonk]

I believe magic_quotes_gpc is turned on (and should be left on for security reasons and cleaner code?).

 

How do you remove escapes before using mysql_real_escape_string?

 

I tried height = '" . stripslashes(mysql_real_escape_string($height)) . "' but same results.

[PFMaBiSmAd]

and should be left on for security reasons and cleaner code?

 

No, it does not escape all the possible characters, which is why you are using mysql_real_escape_string on the data, and it is being eliminated in the next major php version release.

 

$var = 'something from somewhere that you need to escape using mysql_real_escape_string';
if (get_magic_quotes_gpc()) {
    // remove the escaping that magic_quotes_gpc did
    $var = stripslashes($var);
}
// use mysql_real_escape_string on the data
$var = mysql_real_escape_string($var);

 

 

[eMonk]

I just got my host to turn magic_quotes_gpc OFF but same results.

 

displays the data:

 

5'8 (but 5\'8\" is displayed in the table column. No double quote at the end).

 

Also using stripslashes() for display.

[eMonk]

few mins.. just checked phpinfo () and its still turned on.

[eMonk]

Ok, now its off but same results...

[PFMaBiSmAd]

Are you getting just the 5'8" inserted into the database table, with no \ characters in the database table?

[eMonk]

Are you getting just the 5'8" inserted into the database table, with no \ characters in the database table?

 

That is correct, no \ characters in the database table now.

 

I just added echo $height; and it displayed the data correctly but in the text field box it shows 5'8

 

<input name="height" type="text" size="2" value="<?=$height?>" maxlength="6">

 

Is this normal or something wrong with the code?

 

[PFMaBiSmAd]

If you reread my replies in this thread, I first told you and then posted code that shows what you need to do. It involves using htmlentities() so that the quotes don't break the HTML on your page.

[eMonk]

Ah, that's working now.

 

1 last question...

 

Should I turn magic_quotes_gpc back on? I'll make sure to insert addslashes in form fields but have other scripts on the server and not sure if there are open to sql injections now.

[PFMaBiSmAd]

Should I turn magic_quotes_gpc back on?

 

Why would you do that. magic_quotes_gpc are going to be completely removed from php soon because they DON'T protect against all sql injection. That's why you are using mysql_real_escape_string in your code and I posted another code example that shows how your code can test if magic_quotes_gpc is on (it also tests if it is even present) and correctly removes the BAD escaping that php does so that you can CORRECTLY escape the data using mysql_real_escape_string.

[eMonk]

Thanks again PFMaBiSmAd. I have learned from this and starting to like this place.