Apostrophes in Form Fields

[kuau]

I have a form into which customers enter their first and last names. A php script loads the values into a mySQL table. If their name contains an apostrophe (eg. O'Brian), I tell them to put two apostrophes in a row. Is there a more elegant way to handle this problem? I tried using double quotes as the php delimiter but it caused even more problems.

Would really appreciate some insight. Thanks. erin :)

[Twey]

You should be using mysql_real_escape_string(). stripslashes() may also be necessary depending on configuration (if you need to apply it, make sure you do so before mysql_real_escape_string()).

[kuau]

Where should I be using it... where I load the values? For example:

$sql = "INSERT INTO booking (...Book_First_Name,Book_Last_Name,...)

VALUES (... 'mysql_real_escape_string($First_Name)', 'mysql_real_escape_string($Last_Name), .... ";

I know the former programmer tried stripslashes() somewhere regarding a similar problem and it didn't prevent error messages when there was an apostrophe. I thought stripslashes was for if you inserted slashes before special characters and then had to remove them prior to display, but how do you insert them when it is the client doing the typing? (sorry for these lame questions). Aloha, erin :)

[Twey]

When you define $First_Name and $Last_Name (which variable names, by the way, go against PHP convention such as it is). You can use a helper array:
$v = array_map('mysql_real_escape_string', $_POST);
I know the former programmer tried stripslashes() somewhere regarding a similar problem and it didn't prevent error messages when there was an apostrophe. I thought stripslashes was for if you inserted slashes before special characters and then had to remove them prior to displaySometimes, depending on server config, they're inserted automatically where you don't want them, and mess things up.

[kuau]

I inherited this code from some guy who refused to maintain the site (I can see why) so the client came to me. I always use lowercase letters in php and mySQL... I'm assuming that's what you mean but he didn't. I'm afraid to change things too much in case the site crashes and burns. Most things pretty much work so I just try to fix the things that don't because I had never laid eyes on php, js, or mySQL before this.

I found this in the php... is this what you mean?

global $First_Name;
global $Last_Name;

Or do you mean in the html where the form fields are created?
<input type="text" name="Last_Name" size="25" style="font-size: 10px">

I have never encountered $_POST in his code so have not learned about how to use that yet. This is no doubt the worst way to learn, from someone's bad code. He did tell me that his code would not work in php 5 due to security violations (?). Something to look forward to I guess. Thanks for your help. :)

[djr33]

If "register globals" is set in php.ini, it will convert all values of the $_POST array (global form data from what was sent) automatically.
Instead of $_POST['fieldname'], $fieldname can be used.
Lazy and easy, yes, but also can become confusing.
If the system is already setup, don't worry about it.

After the global $var; structure, just do it there.
global $var;
$var = stripslashes($var);
$var = mysql_real_escape_string($var);

etc.

Hope this gets you started.

[Twey]

Lazy and easy, yes, but also can become confusing.And insecure -- thus, it's turned off in a lot of installations, where that code will not work.

[djr33]

Indeed, though I infer it would take rewriting the whole site to remove this reliance.

[Twey]

It is worth it, however.

[kuau]

I checked the php.ini (php4) and register_globals is on (although I did not turn it on). Is that the default for php4?

Twey was saying it is better to turn register_globals off (?) but I don't know enough php to anticipate and fix the repercussions of doing so across the site. And the client won't want to pay for it. There is no sensitive info taken or stored with the site, so, leaving well enough alone, is this what I do?

global $First_Name;
global $Last_Name;
$First_Name = stripslashes($First_Name);
$First_Name = mysql_real_escape_string($First_Name);
$Last_Name = stripslashes($Last_Name);
$Last_Name = mysql_real_escape_string($Last_Name);

If the above is correct, is that the only thing I have to do? I understand the stripslashes (I think), but what exactly does mysql_real_escape_string do? Interpret the entire contents of the variable as a text string?

Thanks so much for your help. Aloha, erin :)

[djr33]

stripslashes removes the slashes added to the sent data that was meant to make it not a security threat (by placing a slash before any harmful command).

mysql_real_escape_string is a strangely named command that makes data safe for input into mysql. Without it, someone can send "; DROP TABLE `table`" in the data, which would end the first part of the query and execute that, or any other command they'd like, with a bit of planning.


I'm not sure if register_globals is default. I think not. However, it depends on how it was installed. If you do turn it off (good for security, as any variable then can be send by someone through a form), you will have to rewrite any script sthat use it.
Though easier, it's not a good idea to rely on that, so keep that in mind for any pages you add to the site.

I hope this helps.

[kuau]

Dear Daniel: Thanks for explaining. So is this correct?

global $First_Name;
global $Last_Name;
$First_Name = stripslashes($First_Name);
$First_Name = mysql_real_escape_string($First_Name);
$Last_Name = stripslashes($Last_Name);
$Last_Name = mysql_real_escape_string($Last_Name);

And will it solve the problem of someone entering an apostrophe in the form field?

Mahalo, erin :)

[djr33]

Though only testing will prove it, I guess that is right. That code is what I intended, yes.

[kuau]

Dear Daniel: OK, I added the code and tried entering a name with an apostrophe. Unfortunately, although the additional code probably protects against malicious code entires, an apostrophe still causes the name not to load into the database. Because a single quote is the field delimiter, as soon as mySQL see's the apostrophe, it thinks it is the start of the next field. Is there some kind of function or command that would cause the contents to be viewed as an encapsulated unit, sort of like triple quotes?

If you go to www.carrentalhawaii.com and click on the "Free Price Check" button, it will take you to the page in question and maybe it'll make more sense. I would think a lot of people would have the same problem. How do other people deal with people's names? There is another place on the site where there is a comment field and you can't even put something like "I'll call you" because of the apostrophe. This is driving me nuts. I sure hope someone has figured this out already. Thanks very much. Aloha, erin :)

[djr33]

I'm not sure then.

You are correct that the apostrophe is ending the string, but that is dealt with when you use mysql_real_escape_string... that's the whole point.

However, you may want to try NOT using stripslashes(), because that might be helping. Try removing that (leave mysql_...) then see what happens.

Troubleshooting this sort of thing can be strange. In theory what I've said should work, but that's... in theory.

[kuau]

Dear Daniel: Please don't think I expect you to second-guess this guy's funky code perfectly. I really appreciate your help - imagine how it is for me when I don't even understand the languages, so just not feeling alone with it is greatly reassuring.

I woke up to a phone message from the client saying that now NONE of the names were loading into the database for anyone (even with no apostrophes), so I had to remove the code. I'll try putting half of it back and see what happens. Now you see why I am so nervous about making changes to a live, ecommerce site.

This is what I just tried:

function replace_email_template_variables($body_text) {
// This function will replace the variables in the Body text for the email.
// This uses several global variables.
global $Today;
global $First_Name;
global $Last_Name;
$First_Name = mysql_real_escape_string($First_Name);
$Last_Name = mysql_real_escape_string($Last_Name);
global $Phone;
global $Email;

And besides loading blanks for the first & last names in the table, the email looks like this:

Dear ,

Whereas when I remove the extra code the email looks like this: Dear aa test aa,

Hopefully that will give you a clue as to why neither comamnd is working properly. Any ideas? Mahalo, erin :)

[Master_script_maker]

try

$First_Name = addslashes($First_Name);
$Last_Name = addslashes($Last_Name);
instead of
$First_Name = mysql_real_escape_string($First_Name);
$Last_Name = mysql_real_escape_string($Last_Name);

[kuau]

YES!! Thank you!! Now it does load names into the database with or without an apostrophe (and loads it without the apostrophe). However, it sends the email looking like this: Dear aa test O\'Brian, so I assume that means I have to use the stripslashes command somewhere before the email gets sent, no? I'll see if I can figure out where. Thanks a million! erin :)

[kuau]

I think this might be where to do it but do I stripslashes from %First_Name%? Not sure what this is.

$body_text = eregi_replace('%Today%', $Today, $body_text);
$body_text = eregi_replace('%First_Name%', $First_Name, $body_text);
$body_text = eregi_replace('%Last_Name%', $Last_Name, $body_text);
$body_text = eregi_replace('%Phone%', $Phone, $body_text);
$body_text = eregi_replace('%Email%', $Email, $body_text);

[kuau]

Is this correct?


$body_text = eregi_replace('%First_Name%', stripslashes($First_Name), $body_text);

[Leafy]

Is this correct?


$body_text = eregi_replace('%First_Name%', stripslashes($First_Name), $body_text);

That looks correct.

Just to give you information, mysql_real_escape_string takes it's argument and escapes it so that it can be put in a query safely. You should use it when ever you are using data derived from the user.

For example, if you were reading their name from the form and they entered

O'; DROP DATABASE customers
And your code looks like this


$customer = $_POST["first_name"];
$query = "SELECT * FROM purchases WHERE `Customer`='$customer$'";

When the query was sent to the SQL database, it looks like this:


SELECT * FROM purchases WHERE `Customer`='O'; DROP DATABASE customers;


Read more about it on Wikipedia under "SQL Injection" (http://www.wikipedia.org/wiki/SQL_injection).

[kuau]

Dear Leafy: Thanks very much for explaining about SQL Injection attacks. Daniel also warned me about this. I understand and would like to protect against this risk. The only problem is that as soon as I add the commands mysql_real_escape_string OR stripslashes to the code, the names totally disappear, ie. it loads blanks into the database where the names would be, or it gives an error message.

Maybe I am putting the commands in the wrong place. Does it matter that it is inside a function? Here is the whole function (minus extraneous variables):


function replace_email_template_variables($body_text) {
global $Today;
global $First_Name;
global $Last_Name;
$First_Name = addslashes($First_Name);
$Last_Name = addslashes($Last_Name);

global $Phone;
global $Email;

$body_text = eregi_replace('%Today%', $Today, $body_text);
$body_text = eregi_replace('%First_Name%', $First_Name, $body_text);
$body_text = eregi_replace('%Last_Name%', $Last_Name, $body_text);
$body_text = eregi_replace('%Phone%', $Phone, $body_text);
$body_text = eregi_replace('%Email%', $Email, $body_text);

return ($body_text);
}

Where does the mysql_real_escape_string go, and the stripslashes? Mahalo, erin :)