Trouble Stripping Special Characters With Post Data

MySQL database
#1

I’m working on a script where the user fills outs a form and then it is added to a sql database. I know the script works without special characters, because if I type “1” into all the fields, I get no errors. If I actually fill out the form truthfully, I get the following 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 'Falls, 12/28/1989, 5\'8\", blonde blue, granty, ear, tongue, zac efron, Mom - M' at line 2

Here is what I’m using to write the data to the db:

<?php mysql_connect("localhost", "*****", "*****") or die(mysql_error()); mysql_select_db("*****") or die(mysql_error()); //define variables $andrea_birthplace = $_POST['andrea_birthplace']; $andrea_birthday = $_POST['andrea_birthday']; $andrea_height = $_POST['andrea_height']; $andrea_eyehaircolor = $_POST['andrea_eyehaircolor']; $andrea_nickname = $_POST['andrea_nickname']; $andrea_piercingstattoos = $_POST['andrea_piercingstattoos']; $andrea_celebritycrush = $_POST['andrea_celebritycrush']; $andrea_parents = $_POST['andrea_parents']; $andrea_siblings = $_POST['andrea_siblings']; $andrea_fullname = $_POST['andrea_fullname']; $andrea_actor = $_POST['andrea_actor']; $andrea_band = $_POST['andrea_band']; $andrea_magazine = $_POST['andrea_magazine']; $andrea_movie = $_POST['andrea_movie']; $andrea_tvshow = $_POST['andrea_tvshow']; $andrea_cartoon = $_POST['andrea_cartoon']; $andrea_animal = $_POST['andrea_animal']; $andrea_car = $_POST['andrea_car']; $andrea_color = $_POST['andrea_color']; $andrea_food = $_POST['andrea_food']; $andrea_season = $_POST['andrea_season']; $andrea_holiday = $_POST['andrea_holiday']; $andrea_songtoperform = $_POST['andrea_songtoperform']; $andrea_performancetodate = $_POST['andrea_performancetodate']; $andrea_singer = $_POST['andrea_singer']; $andrea_actress = $_POST['andrea_actress'];

//clean them
$andrea_birthplace = htmlentities($andrea_birthplace);
$andrea_birthday = mysql_real_escape_string($andrea_birthday);
$andrea_height = mysql_real_escape_string($andrea_height);
$andrea_eyehaircolor = mysql_real_escape_string($andrea_eyehaircolor);
$andrea_nickname = mysql_real_escape_string($andrea_nickname);
$andrea_piercingstattoos = mysql_real_escape_string($andrea_piercingstattoos);
$andrea_celebritycrush = mysql_real_escape_string($andrea_celebritycrush);
$andrea_parents = mysql_real_escape_string($andrea_parents);
$andrea_siblings = mysql_real_escape_string($andrea_siblings);
$andrea_fullname = mysql_real_escape_string($andrea_fullname);
$andrea_actor = mysql_real_escape_string($andrea_actor);
$andrea_band = mysql_real_escape_string($andrea_band);
$andrea_magazine = mysql_real_escape_string($andrea_magazine);
$andrea_movie = mysql_real_escape_string($andrea_movie);
$andrea_tvshow = mysql_real_escape_string($andrea_tvshow);
$andrea_cartoon = mysql_real_escape_string($andrea_cartoon);
$andrea_animal = mysql_real_escape_string($andrea_animal);
$andrea_car = mysql_real_escape_string($andrea_car);
$andrea_color = mysql_real_escape_string($andrea_color);
$andrea_food = mysql_real_escape_string($andrea_food);
$andrea_season =mysql_real_escape_string($andrea_season);
$andrea_holiday = mysql_real_escape_string($andrea_holiday);
$andrea_songtoperform = mysql_real_escape_string($andrea_songtoperform);
$andrea_performancetodate = mysql_real_escape_string($andrea_performancetodate);
$andrea_singer = mysql_real_escape_string($andrea_singer);
$andrea_actress = mysql_real_escape_string($andrea_actress);
mysql_query("INSERT INTO *****
(birthplace, birthday, height, eyehaircolor, nickname, piercingstattoos, celebritycrush, parents, siblings, fullname, actor, actress, singer, band, magazine, movie, tvshow, cartoon, animal, car, color, food, season, holiday, songtoperform, performancetodate) VALUES($andrea_birthplace, $andrea_birthday, $andrea_height, $andrea_eyehaircolor, $andrea_nickname, $andrea_piercingstattoos, $andrea_celebritycrush, $andrea_parents, $andrea_siblings, $andrea_fullname, $andrea_actor, $andrea_actress, $andrea_singer, $andrea_band, $andrea_magazine, $andrea_movie, $andrea_tvshow, $andrea_cartoon, $andrea_animal, $andrea_car, $andrea_color, $andrea_food, $andrea_season, $andrea_holiday, $andrea_songtoperform, $andrea_performancetodate ) ")
or die(mysql_error());

Once agan, I know that my table is setup correctly, because when I enter just “1” for every field, everything runs smoothly. I have tried mysql_real_escape_string and it did not work. I was connected to a database and I did an ifelse function to make sure that that function was available. I consider myself an educated beginner to intermediate level, and I have been trouble shooting this script for about 3 hours, and I am at my wits end. I appreciate any feedback in advance!

#2

I accidentally double posted, and can’t seem to figure out how to delete the second.

#3

If there are text fields in your table, you need to use quotes to surround text values, like this:

mysql_query("INSERT INTO ***** (nickname,piercingstattoos) VALUES ('$andrea_nickname','$andrea_piercingstattoos')");

Assuming that you have escaped special chars using mysql_real_escape_string(), and also you have magic_quotes_gpc = Off to avoid double escaping.

#4

That actually worked (and I’m kicking myself), but now it only puts a value of “0” when I recall the information to put it on another page. Here is the code I’m using:

mysql_connect("localhost", "*****", "*****") or die(mysql_error()); mysql_select_db("*****") or die(mysql_error()); $results = mysql_query("SELECT * FROM andrea_results") or die(mysql_error()); $row = mysql_fetch_array( $results );

And I use this to call it:

<?php echo($row['parents']); ?>

Now, I know the POST data information is carrying over correctly, because on the page that says “Data Submitted”, it shows what you entered on the previous page, using the $_POST[‘variables’]. So, somewhere in the insert process, everything becomes a “0”. I’ve updated my insert code:

mysql_query("INSERT INTO *****
(birthplace, birthday, height, eyehaircolor, nickname, piercingstattoos, celebritycrush, parents, siblings, fullname, actor, actress, singer, band, magazine, movie, tvshow, cartoon, animal, car, color, food, season, holiday, songtoperform, performancetodate) VALUES(’$andrea_birthplace’, ‘$andrea_birthday’, ‘$andrea_height’, ‘$andrea_eyehaircolor’, ‘$andrea_nickname’, ‘$andrea_piercingstattoos’, ‘$andrea_celebritycrush’, ‘$andrea_parents’, ‘$andrea_siblings’, ‘$andrea_fullname’, ‘$andrea_actor’, ‘$andrea_actress’, ‘andrea_singer’, ‘$andrea_band’, ‘$andrea_magazine’, ‘$andrea_movie’, ‘$andrea_tvshow’, ‘$andrea_cartoon’, ‘$andrea_animal’, ‘$andrea_car’, ‘$andrea_color’, ‘$andrea_food’, ‘$andrea_season’, ‘$andrea_holiday’, ‘$andrea_songtoperform’, ‘$andrea_performancetodate’ ) ")
or die(mysql_error());

#5

Probably this is what you html form actually submit to php script? Try debugging - at the top of php script add this code to see what values are submitted:
[php]
if(is_array($_POST)){
echo ‘

’;

print_r($_POST);

echo ‘
’;
}
[/php]
#6

When I do that, all my variables show up as defined in the form. It might be easier to see what I mean if you look at it:

http://kandrea.org/survery/andrea_survery.php

That is the original form (yes, it’s spelt “survery” on purpose, lol).

When you submit the form, it takes you to the second page, where it actually inserts into the table. You can see where I’ve tried to recall the db info here:

http://kandrea.org/survery/connect.php

#7

If data from the form is submitting properly, but value still 0, you need to check your database table field type. If you’re saving text value there, the mysql field type must be either: char, varchar, or text.

#8

This is what I get for trying to code at 4:00 in the morning :stuck_out_tongue:

Thank you for holding my hand through that, lol!

Sponsor our Newsletter | Privacy Policy | Terms of Service