MySQL syntax error

Mr_Chew

The registration page:

<form method="post" action="adduser.php"
<table width="200" border="0" align="center" cellpadding="0" cellspacing="3">
  <tr>
    <td><div align="left">Name: </div></td>
    <td><input type="text" name="name" maxlength="15"></td>
  </tr>
  <tr>
    <td><div align="left">Username: </div></td>
    <td><input type="text" name="user" maxlength="15"></td>
  </tr>
  <tr>
    <td><div align="left">Password: </div></td>
    <td><input name="pass" type="password" id="pass" maxlength="15"></td>
  </tr>
  <tr>
    <td><div align="left">E-mail:</div></td>
    <td><p>
      <input name="email" type="text" id="email" maxlength="30">
    </p>
    </td>
  </tr>
  <tr>
    <td colspan="2"><div align="center">
      <input name="Submit" type="submit" id="Submit" value="Submit">
      <input type="reset" name="Reset" value="Reset">
    </div></td>
  </tr>
</table>

The php script:

<?PHP
if ((!$_POST['name']) || (!$_POST['user']) || (!$_POST['pass']) || (!$_POST['email']))
	{
	header("Location: http://www.domain.net/index.php?id=signup");
	exit;
	}
$dbaseuser = "domain_main";
$dbasepassword = "pw";
$dbasename = "domain_users";
$connection = mysql_connect("localhost", $dbaseuser, $dbasepassword)
	or die(mysql_error());
$db = mysql_select_db($dbasename, $connection)
	or die(mysql_error());
$sql = "INSERT into users (0, name, user, pass, email)
		VALUES (0, '$_POST[name]', '$_POST[user], password('$_POST[pass]')), '$_POST[email]'";
$result = mysql_query($sql, $connection)
	or die(mysql_error());
?>

HTML>
<HEAD>
<TITLE>Add a User</TITLE>
</HEAD>
<BODY>
Thank you,<? echo "$_POST[name]";?>. You have been successfully added to
  the database. You may now <a href="www.domain.net/index.php?id=main">login</a>
.  

<P>Name:<BR>
<? echo "$_POST[name]"; ?></p>

<P>Username:<BR>
<? echo "$_POST[user]"; ?></p>

<P>Password:<BR>
<? echo "$_POST[pass]"; ?></p>

<P>Email:<BR>
<? echo "$_POST[email]"; ?></p>

</BODY>
</HTML>

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 '0, name, user, pass, email) VALUES (0, 'test', 'test, password('test')), 'tes' at line 1

In my users table i have my first field named 'id' which i made the primary and auto increments...that is why I put the 0 in the sql query which apparantly isn't correct. Any help would be deeply appreciated.

notaloser

just completely take out the 0's, you dont need them

$sql = ("INSERT into users (name, user, pass, email) 
        VALUES ('$_POST[name]', '$_POST[user]', password('$_POST[pass]'), '$_POST[email]'"));

Mr_Chew

yet the id field will still auto increment so duplicate account names can't be made?

edit*

I just tried taking away the 0's in the query and received this MySql syntax 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 'test'), 'test'' at line 2

$sql = "INSERT into users (name, user, pass, email)
		VALUES ('$_POST[name]', '$_POST[user], password('$_POST[pass]'), '$_POST[email]'";

toplay

Problem is here:

'$_POST[user],

change to:

'$_POST[user]',

You forgot a quote.

🙂

Mr_Chew

I fixed that but I think I'm still missing something else that I can't figure out--

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 2

Anyone?

toplay

Well, post the current SQL that you are using. Always do that when posting errors!!

Mr_Chew

$sql = "INSERT into users (name, user, pass, email)
VALUES ('$POST[name]', '$POST[user]', password('$POST[pass]'), '$POST[email]'";

there it is

toplay

You forgot the closing right parenthesis to match the opening one in VALUES.

$sql = "INSERT into users (name, user, pass, email)
VALUES ('{$_POST['name']}', '{$_POST['user']}', password('{$_POST['pass']}'), '{$_POST['emai'l]}')";

Mr_Chew

I noticed when I try to create a duplicate account to see what happens, I get an error page upon hitting submit that says "patch your server". I created a field in my table with an integer value named 'id' that auto_increments. My intention was to have the increment numbers to be assigned to each user account so that no account duplication occurs. What am I to do?

toplay

Check with your web host provider about the error (patch your server).

Mr_Chew

Okay, what about the auto_increment 'id' integer field? Don't I need to incorporate that in my SQL query?

toplay

Mr Chew wrote:
Okay, what about the auto_increment 'id' integer field? Don't I need to incorporate that in my SQL query?

auto_increment is just that. It will increment the number automatically every time you insert. Just check your table to verify it.

Roger_Ramjet

Just my 2ps worth - from the manual

"An upgrade to MySQL 4.1 can cause a compatibility issue for applications that use PASSWORD() to generate passwords for their own purposes. Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts. But some applications use PASSWORD() for their own purposes anyway."

What that means is if your host upgrades the mysql version your passwords could no longer work. So use md5 or sha1 to store them and not password.

toplay

Yes, I agree it's not great to use PASSWORD(), however, it they upgrade to 4.1 or higher, then they can use OLD_PASSWORD() instead. Quote from manual:

http://dev.mysql.com/doc/mysql/en/encryption-functions.html#id3009019 wrote:
OLD_PASSWORD() is available as of MySQL 4.1, when the implementation of PASSWORD() was changed to improve security. OLD_PASSWORD() returns the value of the pre-4.1 implementation of PASSWORD().

http://dev.mysql.com/doc/mysql/en/password-hashing.html

🙂

Mr_Chew

Thank you for that information. Where would i implement the md5() function for the password in my script?

Roger_Ramjet

Remove the password() function from your sql string and replace it with an embedded var that contains the hashed password

$passhash = md5($_POST['pass']);

$sql = "INSERT into users (0, name, user, pass, email)
        VALUES (0, '$_POST[name]', '$_POST[user], '$passhash', '$_POST[email]'";

Make sure that you have set the column size to 32 chars to hold the hash string