PHP + MySQL Escaping Special Characters IMPORTANT..Please

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
captainoats
Forum Newbie
Posts: 4
Joined: Fri Jun 29, 2007 3:44 am

PHP + MySQL Escaping Special Characters IMPORTANT..Please

Post by captainoats »

I have this variable which is a user input comes from a text box

$item=$_POST['item']; // sponge bob's & crabby# patty

I insert $item into the table without any problems.

Now I want to use it in a SELECT QUERY.

Lets say we are on on a different PHP page and I retrieved $item so I could use it for an SQL SELECT QUERY.


$tempQuery=mysql_query("SELECT * from itemtable where item_name= '$item' ");

Remember : $item contains an apostrophy(') ampersand(&) and pound sign(#)

How do I escape these characters so that my query will be executed properly. Now Im getting sql error coz of those special characters.

Im using PHP5 + MYSQL

I would like to know how to escape all the special characters.

In plain english hehehe...Im kinda new at this...THANKS
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

indeed, you should apply mysql_real_escape_string() or intval() (or sister function), depending if your expecting a string or integer value, to all incoming data, always.
captainoats
Forum Newbie
Posts: 4
Joined: Fri Jun 29, 2007 3:44 am

now just how do i apply it?

Post by captainoats »

could you tell me how to use that function.. using my example $item


$tempQuery=mysql_query("SELECT * from ITEMTABLE where item_name='$item'");




hehe..thanks Id appreciate it if you guys help me. Im so stressed try everything @_@ I could really use some examples
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post by mentor »

Did you visit superdezign's provided PHP manual link?

This is how you can use this

Code: Select all

$tempQuery=mysql_query("SELECT * from ITEMTABLE where item_name='".mysql_real_escape_string($item)."'");
captainoats
Forum Newbie
Posts: 4
Joined: Fri Jun 29, 2007 3:44 am

thanks

Post by captainoats »

thanks...let me try it out...tell you if it works...ciao!!!


BTW do i have to turn on magicquotes or anything?
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

Try it out, if not your sever won't explode, your family will still know you and nobody will have stolen your car. The worst that can happen is that you get an error message... :roll:
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

However ...the cat might get pregnant.
captainoats
Forum Newbie
Posts: 4
Joined: Fri Jun 29, 2007 3:44 am

Working But some chars causes Problems

Post by captainoats »

#
&
+

I found out that these characters are not solved by mysql_real_escape_string()

The rest are okay...

Any ideas how to escape these characters...? Thanks
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

you could write a custom preg_match()...

http://us2.php.net/manual/en/function.preg-match.php
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

ReverendDexter wrote:you could write a custom preg_match()...

http://us2.php.net/manual/en/function.preg-match.php
... preg_replace().
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

why do you seen to escape + and & ?
Post Reply