Managing Users with PHP Sessions and MySQL

[treacle0996]

Have just used Kevin Yanks tutorial http://www.sitepoint.com/article/use...essions-mysql/ to set up user access.

A fantastic tutorial, but there are two points which just don't work for me.

1 - If i use <form method="post" action="<?=$_SERVER['PHP_SELF']?>">
the page isn't called again, it just appends <?=$_SERVER['PHP_SELF']?> to the url
if i use <?php echo $_SERVER['PHP_SELF']; ?> it works fine


2 - the mysql PASSWORD function causes an error
If i use $sql = "INSERT INTO user SET
userid = '$_POST[newid]',
password = PASSWORD('$newpass'),
fullname = '$_POST[newname]',
email = '$_POST[newemail]',
notes = '$_POST[newnotes]'"; i get a mysql error

but, if i then copy the sql string generated and exec it in sqlyog it works fine!
If i remove the PASSWORD function it works


I am guessing they are both down to php.ini settings ???

[guido2004]

1 - If i use <form method="post" action="<?=$_SERVER['PHP_SELF']?>">
the page isn't called again, it just appends <?=$_SERVER['PHP_SELF']?> to the url
if i use <?php echo $_SERVER['PHP_SELF']; ?> it works fine

Yes, I guess it's a setting, but don't worry about it. You should not use short tags anyway.

2 - the mysql PASSWORD function causes an error
If i use $sql = "INSERT INTO user SET
userid = '$_POST[newid]',
password = PASSWORD('$newpass'),
fullname = '$_POST[newname]',
email = '$_POST[newemail]',
notes = '$_POST[newnotes]'"; i get a mysql error

What error?

[lorenw]

just a guess, change these

password = PASSWORD('$newpass'),
to
password = PASSWORD('$_POST[newpass]'),

I'm guessing password is not null and you probably have globals off which is good but $newpass does not exist unless you call it by $_POST

[treacle0996]

Good point. i'd forgotten i could look at mysql_error

sql is: INSERT INTO users SET userid = 'jjjj', password = PASSWORD('139675'), fullname = 'jjjj', email = 'jjjj', notes = ''

Error is: Data too long for column 'password' at row 1

password is char16.

sql works fine when run directly on data

[AnthonySterling]

You're using SET with no WHERE clause, are you trying to update a current user? If so you should be passing a unique row identifier too.

[treacle0996]

$newpass does exist. The user has just entered the other variables, and i generated the password - $newpass = substr(md5(time()),0,6);


I'm inserting, not updating.

[AnthonySterling]

If you're inserting a new entry, then your SQL should really be something like...

<?php
$sSQL = sprintf("INSERT INTO user (userid, password, fullname, email, notes)VALUES('%s', PASSWORD('%s'), '%s', '%s', '%s') LIMIT 1",
    mysql_real_escape_string($_POST['newid']),
    $newpass,
    mysql_real_escape_string($_POST['newname']),
    mysql_real_escape_string($_POST['newmail']),
    mysql_real_escape_string($_POST['newnotes'])
);

[guido2004]

If you're inserting a new entry, then your SQL should really be something like...

No, I thought so too, but I checked the MySQL documentation, and the SET syntax is allowed too. It would have given an error about it if it hadn't been allowed.

If the PASSWORD() function is the problem, consider using another function.
I quote the MySQL reference guide (http://dev.mysql.com/doc/refman/5.1/...ion_password):

"Note

The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or SHA1() instead"

[treacle0996]

Thanks for the help.

Sort of found out what was happening, but still don't know why.

Field length of 16 was too short for the whole password. When sql run directly it just truncated it, when run via php it complained.

When increased to char100 all worked fine.

No idea why, but won't worry out about it anymore. Will try one of the recommended ways instead.

Cheers.