[SOLVED] Problem with MySQL Query

[LemonInflux]

<?php
// Added php wrappers for highlighting...
	$user = mysql_real_escape_string($_POST['username']);
	$pass = md5(mysql_real_escape_string($_POST['password']));
	$mail = mysql_real_escape_string($_POST['e-mail_address']);
	$sql = mysql_query("INSERT INTO `". MEMBERS ."` (`userid`, `username`, `password`, `posts`, `avatar`, `signature`, `msn`, `aim`, `website`, `IP`, `email`, `rank`, `last_active`, `birthday`, `location`, `interests`, `title`) VALUES (NULL, '". $user ."', '". $pass ."', \'\', \'\', NULL, NULL, NULL, NULL, '". $_SERVER['REMOTE_ADDR'] ."', '". $mail ."', '4', CURRENT_TIMESTAMP, NULL, NULL, NULL, NULL") or die(mysql_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 '\'\', \'\', NULL, NULL, NULL, NULL, '127.0.0.1', 'ah@ah.com', '4', CURRENT_TIMES' at line 1

 

I'm not seeing the error :/ The php query was generated by PHPMyAdmin. Anyone have any ideas?

[pranav_kavi]

U dont need to escape the single quotes.Hence,shud b

 

$sql = mysql_query("INSERT INTO `". MEMBERS ."` (`userid`, `username`, `password`, `posts`, `avatar`, `signature`, `msn`, `aim`, `website`, `IP`, `email`, `rank`, `last_active`, `birthday`, `location`, `interests`, `title`) VALUES (NULL, '". $user ."', '". $pass ."', '', '', NULL, NULL, NULL, NULL, '". $_SERVER['REMOTE_ADDR'] ."', '". $mail ."', '4', CURRENT_TIMESTAMP, NULL, NULL, NULL, NULL") or die(mysql_error());

[kratsg]

	$sql = mysql_query("INSERT INTO `". MEMBERS ."` (`userid`, `username`, `password`, `posts`, `avatar`, `signature`, `msn`, `aim`, `website`, `IP`, `email`, `rank`, `last_active`, `birthday`, `location`, `interests`, `title`) VALUES (NULL, '". $user ."', '". $pass ."', \'\', \'\', NULL, NULL, NULL, NULL, '". $_SERVER['REMOTE_ADDR'] ."', '". $mail ."', '4', CURRENT_TIMESTAMP, NULL, NULL, NULL, NULL") or die(mysql_error());

 

Not quite sure why you escaped... ALL THAT STUFF, it should be something like this:

 

$query = "INSERT INTO `MEMBERS` (`userid`, `username`, `password`, `posts`, `avatar`, `signature`, `msn`, `aim`, `website`, `IP`, `email`, `rank`, `last_active`, `birthday`, `location`, `interests`, `title`) VALUES (NULL, '$user', '$pass','','',NULL,NULL,NULL,NULL,'".$_SERVER['REMOTE_ADDR']."', '$mail','4','".time()."',NULL,NULL,NULL,NULL";
$query = mysql_query($query) or die(mysql_error());

 

I also fixed up some combos of the double/single quotes a little bit...

[LemonInflux]

Sorry, I have security OCD I'll try that.

[LemonInflux]

Didn't work:

 

'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'

[LemonInflux]

Have uploaded the project so people can look:

 

http://www.reflexprojects.net/ - the project

http://www.reflexprojects.net/register.php - the problem

[LemonInflux]

do I need the time() thing, or can I just NULL that?

[LemonInflux]

current query:

 

<?php

$user = mysql_real_escape_string($_POST['username']);
	$pass = md5(mysql_real_escape_string($_POST['password']));
	$mail = mysql_real_escape_string($_POST['e-mail_address']);
	$query = "INSERT INTO `". MEMBERS ."` (`userid`, `username`, `password`, `posts`, `avatar`, `signature`, `msn`, `aim`, `website`, `IP`, `email`, `rank`, `last_active`, `birthday`, `location`, `interests`, `title`) VALUES (NULL, '". $user ."', '". $pass ."', NULL, NULL, NULL, NULL, NULL, NULL,'". $_SERVER['REMOTE_ADDR'] ."', '". $mail ."','4','". time() ."', NULL, NULL, NULL, NULL";
	$query = mysql_query($query) or die(mysql_error());

?>

 

And still unsolved

[Gamic]

<?php
$sql="insert into members(
username, password, IP, email, rank, last_active
)values(
'$user',PASSWORD('$pass'),'{$_SERVER['REMOTE_ADDR']}','$mail','4','".time()."'
);";
?>

 

Ok, there are several things going on now, with this query. I have removed each NULL and each field that null was being inserted into. Unless there is some reason to have these they are not needed. I have added "PASSWORD ('$pass')" instead of just "'$pass'". This is so that you don't store the password. When checking a user login you would do something like

select user 
from members 
where username='foo'
and password=PASSWORD('bar');

.

 

Finally the {$_ARRAY['fooBar']} works just fine, and will help if you are having trouble escaping the single or double quotes in the right place.

[LemonInflux]

so what did PASSWORD do? I have md5 anyway?

[trq]

Do not use mysql's PASSWORD function. It is an internal function used by mysql to store passwords and not intended for use in client code.

[LemonInflux]

Okay.

[LemonInflux]

Yay, it worked (didn't use password). Topic solved!

[Gamic]

so what did PASSWORD do? I have md5 anyway?

 

PASSWORD() is a mysql function that encrypts some plain text. It is used so that you don't store plain text passwords in your database. This is good for several reasons (for which I'll suggest listening to Secruity Now). It is perfectly acceptable for a client to use this function inside an sql query as it is not the client that resolves the function, but the server.

 

I am not sure if md5 is just as good (But I am sure that someone will point me in to a good article), but at least you are not storing passwords in plain text. But don't, for any reason, think that it is unaccaptable to use something just because it is implemented in the database.

 

However, and this is important, I am assuming that the database client and the database server are on the same physical machine. Using a combination of some php md5() function and mysql's password() function could lead to the best comprimise.

 

*as an aside, by thorpe's logic you should never use count(), sum() or any function in mysql just because it is an internal function used to count(numberOfrows) or sum(someColumn).

 

But yes, glad to hear that it's working now.

[revraz]

The con of using password is that your code is not portable.  What if you update your DB to a new version and now all of a sudden none of your PW's work?

 

md5 would be better, and sha1 even better still.  Search google for md5 vs sha1 and decide for yourself.