Welcome to WebmasterWorld Guest from 54.156.92.138

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Escape characters in php and mysql

     
9:44 am on Sep 8, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 28, 2010
posts:26
votes: 0


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
10:32 am on Sept 8, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


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
4:46 pm on Sept 8, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 28, 2010
posts:26
votes: 0


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!
7:17 pm on Sept 8, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


Hi there wincode,

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

Cheers,
MRb
9:37 pm on Sept 8, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 28, 2010
posts:26
votes: 0


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! :)
10:06 pm on Sept 8, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


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
12:10 am on Sept 9, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 28, 2010
posts:26
votes: 0


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
12:12 am on Sept 9, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 28, 2010
posts:26
votes: 0


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
11:07 am on Sept 9, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


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
11:14 am on Sept 9, 2010 (gmt 0)

New User

10+ Year Member

joined:June 3, 2005
posts:21
votes: 0


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.
11:39 am on Sept 9, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


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