Escape characters in php and mysql

[wincode]

Hello. I have a form that registers values into a database. The problem is when the values have apostrophes, but when I echo it on the php pages, the value is returned as \'. How can I solve this problem?
Example:
O'Neil becomes O\'Neil

Thanks for your time and help

[Matthew1980]

Hi there Wincode,

I believe this is known as stripslashes:-

[uk3.php.net ]

and addslashes:-

[uk3.php.net ]

does the opposite.

Hope that gives you what your after

Also, if you are putting user generated info into the db, you might want to run the data through strip_tags() and mysql_real_escape_string() too, this will make any use generated input safer or 'sanitised' so that you get rid of any 'malicious' code injection attempts.

Cheers,
MRb

[wincode]

Ahh perfect! Thanks Matthew.
One question, should I place the stripslashes when the values are being added to the database or when they are being read?

Thanks!

[Matthew1980]

Hi there wincode,

My preference is to do it before you insert to the Db, but it's up to you.

Cheers,
MRb

[wincode]

Hmm. I'm getting this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Adam O'Nako',' at line 2


The code is:

$firstname = stripslashes($_POST['firstname']);
$firstname=ucfirst($firstname);
$lastname=stripslashes($_POST['lastname']);
$lastname=ucfirst($lastname);
$fullname=$firstname." ".$lastname;


$sql = mysql_query("INSERT INTO users (fullname,)
    VALUES('$fullname')") 
    or die (mysql_error());

What am I doing wrong?

Thanks a lot! :)

[Matthew1980]

Hi there Wincode,

Build the query outside the function, this will aid you to debug should you have an issue, seeing as you are building the statement using variables..

$sqlQuery = "INSERT INTO `users` (`fullname`) VALUES ('".$fullname."') ";
$sql = mysql_query($sqlQuery) or die (mysql_error());

Give that a go, you had an extra coma in the field list :)

Cheers,
MRb

[wincode]

Hi Matt,
Now I am getting this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''users' ('fullname') VALUES ('O'Adam O'Nako')' at line 1

[wincode]

By the way, I'm entering
O'Adam as the value for the firstname and
O'Nako as the value for the lastname


Thank you so much for your concern

[Matthew1980]

Hi there wincode,

Make sure that you have the column name an table name spelt correctly an in the right case too, that can often catch people out.

If it still fails, try echoing the sql to screen, then copying and pasting that into your mysql interface (phpmyadmin/query browser etc) to see if you actually get a result (which I think you would)

Hope that helps a little,

Cheers,
MRb

[base64]

Such programming style is insecure! All incoming data must be sanitized before putting it to the database or building output html.

[en.wikipedia.org...]
[en.wikipedia.org...]
[owasp.org...]

I suggest to take php and sql security very seriously or your website will get hacked down sooner or later.

[Matthew1980]

Hi there base64,

Also, if you are putting user generated info into the db, you might want to run the data through strip_tags() and mysql_real_escape_string() too, this will make any use generated input safer or 'sanitised' so that you get rid of any 'malicious' code injection attempts.

There are lots of threads on here wrt sanitising data (sql injection prevention), and majority of people advocate this to anyone who is interested, thanks for the links though, I shall have a read of those over lunch to see if there is anything interesting that I haven't seen before.

Though the last one is relevant to asp, which is another language, but the idea's are the same!

Cheers,
MRb