[SOLVED] absurd insert error

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

[SOLVED] absurd insert error

Post 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.
Last edited by $var on Wed Apr 18, 2007 12:11 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

@ needs to be escaped.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I didn't know that.. learn something new every day.

I guess thats because I always escape my data anyway! :wink:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
:dubious: Never had this problem.. mysql 3, 4 or 5. Any idea what causes that?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

I didn't think user-defined variables were interpreted in a string context. :? confused..
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post 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?
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post 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.
Last edited by $var on Wed Apr 18, 2007 12:04 pm, edited 1 time in total.
User avatar
guitarlvr
Forum Contributor
Posts: 245
Joined: Wed Mar 21, 2007 10:35 pm

Post 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
Last edited by guitarlvr on Wed Apr 18, 2007 12:13 pm, edited 1 time in total.
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post 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.
Post Reply