Page 1 of 2
[SOLVED] absurd insert error
Posted: Tue Apr 17, 2007 4:14 pm
by $var
i'm getting an error inserting an e-mail into a database?!
this is non-sense to me, i feel like i have collected a million addresses on at least 10 different servers and i've never run into this.
it appears to be having troubles reading the @ symbol!?
Code: Select all
$q =
"INSERT INTO hcw_userDetail (
UserDetail_Email
)
VALUES (
'".$_POST["email"]."'
)";
$result = mysql_query($q);
spits out 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 '@thevariables.com' at line 1
does this seem outright bizarre? i've never seen this. i'm using the exact some code in unison on another site with no err.
Posted: Tue Apr 17, 2007 4:33 pm
by John Cartwright
I'm a bit stumped myself, however, you should
always pass user input variables through
mysql_real_escape_string()
If you echo out the query before passing it through mysql_query(), what does it yield?
Posted: Tue Apr 17, 2007 6:29 pm
by RobertGonzalez
@ needs to be escaped.
Posted: Tue Apr 17, 2007 6:37 pm
by John Cartwright
I didn't know that.. learn something new every day.
I guess thats because I always escape my data anyway!

Posted: Tue Apr 17, 2007 6:55 pm
by RobertGonzalez
I am almost certain it does. I ran into a similar problem with MySQL once and had to escape the @ in order to get the queries to work.
Posted: Tue Apr 17, 2007 7:35 pm
by volka
Code: Select all
echo mysql_real_escape_string('@', $conn);
prints just @ for both my utf8 and latin1 database.
Nevertheless mysql_real_escape_string (or similar) is mandatory.
It would also be helpful to print the actual query that failed (when debugging)
Code: Select all
$conn = mysql_connect(.,.,.) or die(mysql_error();
mysql_select_db(., $conn) or die(mysql_error();
$q ="INSERT INTO hcw_userDetail (
UserDetail_Email
)
VALUES (
'" . mysql_real_escape_string($_POST["email"], $conn) . "'
)";
$result = mysql_query($q, $conn) or die(mysql_error().': '.htmlentities($q));
I'd better not ask what a table having only one field UserDetail_Email is good for.
Posted: Wed Apr 18, 2007 2:36 am
by aaronhall
Everah wrote:I am almost certain it does. I ran into a similar problem with MySQL once and had to escape the @ in order to get the queries to work.

Never had this problem.. mysql 3, 4 or 5. Any idea what causes that?
Posted: Wed Apr 18, 2007 3:01 am
by Chris Corbyn
@ has special meaning in MySQL if used outside of string context, but if you're using it in a string it should be fine. More than likely the MySQL parser just choked on where exactly the problem was. But yes, always escape variable data before adding it to a query.
FYI, @ is a variable operator for stored procedures or simply for storing results in complex routines.
Posted: Wed Apr 18, 2007 3:10 am
by aaronhall
I didn't think user-defined variables were interpreted in a string context.

confused..
Posted: Wed Apr 18, 2007 3:14 am
by Chris Corbyn
aaronhall wrote:I didn't think user-defined variables were interpreted in a string context.

confused..
No they're not. I was making a separate informative comment

As I mentioned in my earlier post, I think MySQL's parser is just returning the error at the wrong point. It's not the first time I've seen it point to the wrong place in the query. I imagine it's to do with the way it tokenizes the query or something. Either way, the issue will be fixed with use of mysql_real_escape_string()
Wow we're having such nice weather in the UK at the moment

Bliss.
Posted: Wed Apr 18, 2007 3:23 am
by aaronhall
Ahh, sorry, I got caught up in the @ hoopla. I glanced over the thread and assumed that had been identified as the culprit. Anyway, send some of that nice weather over my way.
Posted: Wed Apr 18, 2007 8:15 am
by $var
volka wrote:
I'd better not ask what a table having only one field UserDetail_Email is good for.
That's a good question. i have my user data split between two tables, one for qualitative setting and one that is more quantitative and user entered. I found it easier to look at having all the int values in one table, and the human entered varchar in another.
I had seen it done in tutorial, and thought that I would give it a try. So far, I have found it to be less cumbersome to work with as there are about 20 fields (and growing) of user variable settings, and another 15 for profile content.
Does that seem unnecessary to most of you?
Posted: Wed Apr 18, 2007 11:52 am
by $var
gah, this makes NO SENSE!
so, i passed it, and it accepted after i escaped it... but not i'm getting the same error calling it!?
I've tried it both escaped and unescaped, and i still get the error!?!?!
Code: Select all
$select = "SELECT * FROM hcw_userDetail WHERE UserDetail_Email = ".mysql_real_escape_string($_POST["email"]);
$result = mysql_query($select, $link) or die (mysql_error() .' : '.htmlentities($link));
Warning: htmlentities() expects parameter 1 to be string
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 '@thevariables.com' at line 1 : SELECT * FROM hcw_userDetail WHERE UserDetail_Email =
meaghan@thevariables.com
hmm... perhaps there should be different quotes. i will try that.
Posted: Wed Apr 18, 2007 11:56 am
by guitarlvr
doesn't seem to be a closing quote:
Code: Select all
$select = "SELECT * FROM hcw_userDetail WHERE UserDetail_Email = ".mysql_real_escape_string($_POST["email"])
should it be:
Code: Select all
$select = "SELECT * FROM hcw_userDetail WHERE UserDetail_Email = ".mysql_real_escape_string($_POST["email"]"")
Wayne
Posted: Wed Apr 18, 2007 12:05 pm
by $var
you're right.
i used this and it worked fine:
Code: Select all
$select = "SELECT * FROM hcw_userDetail WHERE UserDetail_Email = 'mysql_real_escape_string($_POST[email])'";
wow, i learned a bunch from this. escaping being crucial.