Strang MySQL Query Error

[ionik]

$sql = 'SELECT userid FROM '.USER_TABLE.' WHERE password = "'.$encrypted_pass.'" AND username = "'.$username.'"';

// SELECT userid FROM users WHERE password = "md5" AND username = "UserName"

// that the sql that is shown when the script is run 

if( $db->query($sql) )

 

for some reason i keep getting the 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 '' at line 1

 

if i take out AND username = "'.$username.'"

 

it will work...but if i take out

 

password = "'.$encrypted_pass.'" AND

 

get that error again cant fiqure this one out....

[rhodesa]

we can't really help you without knowing the values of the variables. after this line:

$sql = 'SELECT userid FROM '.USER_TABLE.' WHERE password = "'.$encrypted_pass.'" AND username = "'.$username.'"';

put:

print $sql;exit;

 

it will print the generated query. then, put that in a post

[ionik]

// i posted it in the comment....but here it is again

// SELECT userid FROM users WHERE password = "1a3c9b664bf1869546f449522894e5b90e70b99d" AND username = "UserName"

[rhodesa]

My bad...looks fine, just to be safe use backticks and single quotes:

 

$sql = "SELECT `userid` FROM `".USER_TABLE."` WHERE `password` = '{$encrypted_pass}' AND `username` = '{$username}'";

[ionik]

fiqured out the problem.....seems to be a bug with MySQL....

if you try to get the value of a table where its name is the same as what your looking for will result in an error...?

hence

username = "username" results in an error

but

username = "name" works fine....strange

[rhodesa]

does it work with single quotes?

[ionik]

nope....doest work at all....

[BlueSkyIS]

$sql = "SELECT userid FROM ".USER_TABLE." WHERE password = '".$encrypted_pass."' AND username = '".$username."'";

[rhodesa]

I just created a MySQL table using:

CREATE TABLE `table` (
`userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 255 ) NOT NULL ,
`password` VARCHAR( 255 ) NOT NULL
);
INSERT INTO `table` (`username`,`password`) VALUES ('UserName','1a3c9b664bf1869546f449522894e5b90e70b99d');

 

then ran the script:

<?php
  include('db_connect.php');
  define(USER_TABLE,'users');
  $encrypted_pass = '1a3c9b664bf1869546f449522894e5b90e70b99d';
  $username = 'UserName';
  $sql = 'SELECT userid FROM '.USER_TABLE.' WHERE password = "'.$encrypted_pass.'" AND username = "'.$username.'"';
  $r = mysql_query($sql) or die ("Query failed: ".mysql_error());
  print_r(mysql_fetch_assoc($r));
?>

 

It produced no error and the expected results.

 

[ionik]

mmm...wonder why this happend.....might be differences in versions....seems to happen in 5.0.51a....but doesnt happen for me with 5.0.45

[rhodesa]

Interesting. When I get a chance I'll have to upgrade to 5.0.51a and try it again.

 

Anyone out else out there with MySQL 5.0.51a getting the same result?

[BlueSkyIS]

same results with single quotes?

 

$sql = "SELECT userid FROM ".USER_TABLE." WHERE password = '".$encrypted_pass."' AND username = '".$username."'";