Adding apostrophes to a database?

[Punk Rock Geek]

Okay, so in javascript I have a select menu that pops up under certain conditions.  It has lots of options, but to keep this simple, I'll only post the option that is giving me trouble.  Please assume everything else works correctly:

 

<option value='St. John\'s'>St. John\'s</option>

 

After the user selects this value, I use an insert query to  add it to the database.  However, it appears as "St. John" rather than "St. John's".  Any ideas?

[MadTechie]

Can you post how your inserting them, it sounds like your stripping them out

[BobcatM]

<option value='St. John's'>St. John's</option>

 

<?php 
$place = mysql_real_escape_string ($_POST['place']); 


// Then insert it like so
$sql = "INSERT INTO users SET username = '".$place."' "; 


?>

Just rename place with whatever your select name is.

[DarkWater]

You cannot escape apostrophes with \ in HTML.  You need to encode them with like, htmlentities().  The \' is actually closing off the form attribute.

[Punk Rock Geek]

You cannot escape apostrophes with \ in HTML.  You need to encode them with like, htmlentities().  The \' is actually closing off the form attribute.

 

So... keeping in mind that this is written in javascript, I would put...

 

<option value='htmlentities(St. John's)'>St. John\'s</option>

 

?

[Punk Rock Geek]

Can you post how your inserting them, it sounds like your stripping them out

Inserting them doesn't seem to be the problem area, and here's why:  If I keep everything else the same, but instead of using a select menu, I use javascript to bring up an empty text box, and then I type "St. John's" into the box and click submit, it correctly enters the entire word into the database.

 

Only when the apostrophe is in the javascript itself (the select box value)  does it mess up.

[MadTechie]

in that case, your need to escape the quotes

Options are as follows

$text = str_replace("'", "\'", $text);

$text = addslashes($text);

$text = htmlspecialchars($text, ENT_QUOTES);

$text = htmlentities($text, ENT_QUOTES);

 

 

[redarrow]

what does ENT_QUOTES mean please mate?

[Axeia]

quote_style

 

    The optional second argument, quote_style , tells the function what to do with single and double quote characters. The default mode, ENT_COMPAT, is the backwards compatible mode which only translates the double-quote character and leaves the single-quote untranslated. If ENT_QUOTES is set, both single and double quotes are translated and if ENT_NOQUOTES is set neither single nor double quotes are translated.

[Punk Rock Geek]

in that case, your need to escape the quotes

Options are as follows

$text = str_replace("'", "\'", $text);

$text = htmlspecialchars($text, ENT_QUOTES);

 

I've tried both of these, and it's still appearing as "St. John" in the database.  I'm going to try the other two, but just to clarify...

 

Am I supposed to be putting this code after I pull the option value from the javascript?  So something like...

 

$variable = $this->request['selectmenu'];
$variable = htmlspecialchars($variable, ENT_QUOTES);

 

?

[Ken2k7]

Did you use mysql_real_escape_string on the value before you insert it into the database?

[MadTechie]

Okay think this is all going wrong,

your need to show an example as it is starting to sound like a JS problem (either that or your putting things in the wrong place)

[Punk Rock Geek]

Okay think this is all going wrong,

your need to show an example as it is starting to sound like a JS problem (either that or your putting things in the wrong place)

The JS code is too long and confusing for me to post it here.  I've fixed this problem though, even if I sort of had to "cheat" to do it...

 

if ($variable=="St. John"){
$variable="St. John's";
}

 

Lots of people were mentioning mysql_real_escape_string()

 

I believe this is handy for something else I want to do.  I want to also have a blank text box where users can enter information and submit it.  I need to use mysql_real_escape_string(), as it will ensure they cannot type in any malicious code, right?

 

If I didn't use mysql_real_escape_string(), what kind of things could they type in?

[MadTechie]

Read google "SQL Injection" it basically protects against that!

[roopurt18]

DarkWater hit it on the head.  You're placing invalid characters in your attribute and making your [X]HTML invalid.

 

You said your option looked like this:

<option value='St. John\'s'>St. John\'s</option>

 

I don't know where you're generating that markup, from JavaScript or from PHP, but where ever it comes from it needs to look like this when it reaches the browser:

<option value='St. John&#039;s'>St. John's</option>

[Ken2k7]

roopurt18, wouldn't the single quote in St. John's mess up the single quote in the value attribute?

[roopurt18]

I could have sworn I entered it with the proper HTML entity.  I've fixed my original post.  I intended it to say that by the time it hits the browser it should look like this:

 

<option value='St. John&#039;s'>St. John&#039;s</option>

 

(edit) SMF did it again!  I had to edit this one to get it to appear correctly as well.

[Punk Rock Geek]

I could have sworn I entered it with the proper HTML entity.  I've fixed my original post.  I intended it to say that by the time it hits the browser it should look like this:

 

<option value='St. John&#039;s'>St. John&#039;s</option>

 

(edit) SMF did it again!  I had to edit this one to get it to appear correctly as well.

Almost works.  It enters the database as:  St. John&#39;s

[Ken2k7]

Try using html_entity_decode on the POST value first.

[Punk Rock Geek]

Try using html_entity_decode on the POST value first.

Rad, that worked.  Thanks you guys!

[roopurt18]

Glad that you got it sorted, but it sounds to me like you are encoding things one too many times.  I recommend taking the time to learn when and how to properly decode / encode things to save yourself some headache in the future.

[MadTechie]

Thats some good advice i wish i had some time ago..

[Punk Rock Geek]

Glad that you got it sorted, but it sounds to me like you are encoding things one too many times.  I recommend taking the time to learn when and how to properly decode / encode things to save yourself some headache in the future.

html_entity_decode() is the only decoding I did.

[roopurt18]

I could have sworn I entered it with the proper HTML entity.  I've fixed my original post.  I intended it to say that by the time it hits the browser it should look like this:

 

<option value='St. John's'>St. John's</option>

 

(edit) SMF did it again!  I had to edit this one to get it to appear correctly as well.

Almost works.  It enters the database as:  St. John&#39;s

 

Based on that snippet I quoted you are double encoding by the time it gets to the browser.  The reason you had to decode only once is to get rid of the second (and erroneous) encoding that is occurring.  My guess is your PHP is spitting out encoded values and then your JavaScript is encoding them a second time while building the select-tag.