Inserting Textbox Values With Special Characters Into Mysql

[Stoty]

I'm having an issue inserting data from textboxes that have some special characters (periods and slashes) into a mySQL database.

 

Example value inside one of the textboxes: "Butter/Margerine (1/2 cup)"

 

SQL Query (shortened):

 

$sql = "Update tbl123 SET title='".$title."', category='".$category."', ingredient1='".$ingredient1."', ingredient2='".$ingredient2."' WHERE id ='".$id."'";

 

I tried it using just the title (which has no special characters) and it worked fine.

 

Once I added in the ingredient fields (which have special characters), query blows up with this error:

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 ''='Butter/Margarine (1/2 cup)', ingredient9='', ingredient10='', cookstep1='Pre-' at line 1

 

All the "ingredient" fields have a datatype of VARCHAR(40).

 

Is there a function or anything I should add to the query that allows special characters to be inserted into the db?

[trq]

mysql_real_escape_string.

[Stoty]

Would this be the way to implement that:?

 

$sql = mysql_real_escape_string("Update tbl123 SET title='".$title."', category='".$category."', ingredient1='".$ingredient1."', ingredient2='".$ingredient2."' WHERE id ='".$id."'");

[JonnoTheDev]

Always use mysql_real_escape_string on any data that you insert / update / select with if it comes from GET / POST / COOKIE / SESSION data!

 

$sql = "Update tbl123 SET title='".mysql_real_escape_string($title)."', category='".mysql_real_escape_string($category)."', ingredient1='".mysql_real_escape_string($ingredient1)."', ingredient2='".mysql_real_escape_string($ingredient2)."' WHERE id ='".mysql_real_escape_string($id)."'";

Edited November 9, 2012 by neil.johnson

[Stoty]

I'll give that a shot, thanks very much guys!

[Stoty]

I updated my query to this:

$sql = "Update tbl123 SET title='".mysql_real_escape_string($title)."', category='".mysql_real_escape_string($category)."', subcategory='".mysql_real_escape_string($subcategory)."', subcategory2='".mysql_real_escape_string($subcategory2)."', serves='".mysql_real_escape_string($serves)."', preptime='".mysql_real_escape_string($preptime)."', cooktime='".mysql_real_escape_string($cooktime)."', ingredient1='".mysql_real_escape_string($ingredient1)."', ingredient2='".mysql_real_escape_string($ingredient2)."', ingredient3='".mysql_real_escape_string($ingredient3)."', ingredient4='".mysql_real_escape_string($ingredient4)."', ingredient5='".mysql_real_escape_string($ingredient5)."', ingredient6='".mysql_real_escape_string($ingredient6)."', ingredient7='".mysql_real_escape_string($ingredient7)."', ingredient8'='".mysql_real_escape_string($ingredient8)."', ingredient9='".mysql_real_escape_string($ingredient9)."', ingredient10='".mysql_real_escape_string($ingredient10)."', cookstep1='".mysql_real_escape_string($cookstep1)."', cookstep2='".mysql_real_escape_string($cookstep2)."', cookstep3='".mysql_real_escape_string($cookstep3)."', cookstep4='".mysql_real_escape_string($cookstep4)."', cookstep5='".mysql_real_escape_string($cookstep5)."' WHERE id ='".mysql_real_escape_string($id)."'";

 

 

And still get the same error:

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 ''='Butter/Margarine (1/2 cup)', ingredient9='', ingredient10='', cookstep1='Pre-' at line 1

Edited November 9, 2012 by Stoty

[PFMaBiSmAd]

The error is because you have an extra ' after the 8 in ingredient8'

 

I recommend that you build your query statement using sprintf, like in the example at the link to the mysql_real_escape_string function that trq posted, so that the sql syntax is separated as much as possible from the php. This would allow you to more easily see what the sql syntax is.

[Stoty]

Oh wow! haha

Thanks very much for catching that!

All is clear and works great!

 

Thanks again guys!!!!