password encryption within INSERT statement

[ebanbury]

Hi

Hoping someone has some experience with this.
I have an insert record which works perfectly, until I try and encrypt the passwords and set the date to NOW()

When I submit the form - the form does not complete successfully, however a record of some kind is inserted into the table.

The problem is that the fields become out of cync. i.e. the values are not inserted into the correct field.

Any help would be invaluable. I'm using the dreamweaver authentication, but need to tweak it for the obvious security reasons of having to encrpyt the passwords.

Please let me know if you need any more of the code.

Many thanks

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "register")) {
  $insertSQL = sprintf("INSERT INTO user_registration (reg_agent, reg_joindate, reg_lname, reg_fname, reg_companyname, username, password, password2, reg_tel, reg_email) VALUES (%s, NOW(), %s, %s, %s, %s, SHA('password'), SHA('password2'), %s, %s)",
                       GetSQLValueString(isset($_POST['reg_agent']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString($_POST['reg_joindate'], "date"),
                       GetSQLValueString($_POST['reg_lname'], "text"),
                       GetSQLValueString($_POST['reg_fname'], "text"),
                       GetSQLValueString($_POST['reg_companyname'], "text"),
                       GetSQLValueString($_POST['username'], "text"),
                       GetSQLValueString($_POST['password'], "text"),
                       GetSQLValueString($_POST['password2'], "text"),
                       GetSQLValueString($_POST['reg_tel'], "int"),
                       GetSQLValueString($_POST['reg_email'], "text"));

[tiggsy]

I can't see much obviously wrong with the mysql query, assuming that your values aren't in fact out of sync with your column names. One thing that looks a little strange is that you've missed $ off the variable names $password and $password2 in the values list. Since you've obfuscated almost everything else there, too this may just be an oversight.

What i generally do with something like this is to go back to the position where it all works, and then make the smallest possible change and check again, stepping through all the things that need to be changed to get it from where it worked to where I want it to be.

If the worst comes to the worst, you can always insert the record without the password, then amend it with a separate query. Would that work?

I'd also stop trying to use dreamweaver for coding. It's not going to help and may cause problems of its own.

[ebanbury]

Yes thanks I'm also looking to do this without dreamweaver, but my coding is not that good yet...but getting there :-)

In the meantime I've now recreated a much simpler 3 field form to try and see what is happening better...I have just substituted the one date fields with NOW() - but it still moves everything about

The date will go in the date column, but only if I put a letter in the textfield or something so not null. If I leave it null, it will then tell me that username cannot be null, even though I put something in here....very strange....

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO user_registration (reg_joindate, username, password) VALUES (NOW(), %s, %s)",
                       GetSQLValueString($_POST['reg_joindate'], "date"),
                       GetSQLValueString($_POST['username'], "text"),
                       GetSQLValueString($_POST['password'], "text"));

  mysql_select_db($database_iwalletc_localhost, $iwalletc_localhost);
  $Result1 = mysql_query($insertSQL, $iwalletc_localhost) or die(mysql_error());

[tiggsy]

So, why not set the value of reg_joindate outside the query?

$now = date("Y-m-d H:i:s")

But now I look at your code again, I see what's occurring. You're specifying that 2 of the values are replaced, but you've given 3 replacements

This is not a method I use myself, but I guess you should amend your code to:

$insertSQL = sprintf("INSERT INTO user_registration (reg_joindate, username, password) VALUES (NOW(), %s, %s)",
GetSQLValueString($_POST['username'], "text"),
GetSQLValueString($_POST['password'], "text"));

[ebanbury]

Hi There
Thanks for all the help

It actually works if I set the variables and do away with the %s placeholders..

$username =$_POST['username'];
$password =$_POST['password'];
$reg_joindate =$_POST['reg_joindate'];

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO user_reg (username, password, reg_joindate) VALUES ('$username', SHA('$password'), NOW())",
                       GetSQLValueString($_POST['reg_joindate'], "date"),
                       GetSQLValueString($_POST['username'], "text"),
                       GetSQLValueString($_POST['password'], "text"));

Many thanks for the help though....you definitely helped lead me in the right direction by going back step by step :-)

[tiggsy]

I've been using php for several years (must be at least 6) and I still get problems. So I've got used to drilling down in this way, if I can't spot a problem right away. I tend to keep my code really transparent and easy to understand, because if I need to change something 6 months later, it helps if I don't have to work hard to understand what I was doing first.

PS. Can you mark this thread solved, please?