1

I need help in my PHP code running MySQL.

For my stored procedure in MySQL, I have the following:

create procedure register ( out userid              int
                           ,in username             varchar(30)
                           ,in unencryptedpassword  varchar(100)
                           ,in description          varchar(100)
                           ,in emailaddress         varchar(100) )
begin
  declare salt char(25);
  declare createdById int;
  declare createdDate datetime;
  declare lastUpdatedById int;
  declare lastUpdatedDate datetime;
  set salt = 'abcdefghijklmnopqrstuvwxy';
  set createdById = -1;
  set createdDate = now();
  set lastUpdatedById = -1;
  set lastUpdatedDate = now();
  insert into Users ( userId
                    , userName
                    , encryptedPassword
                    , description
                    , emailAddress
                    , createdById
                    , createdDate
                    , lastUpdatedById
                    , lastUpdatedDate ) 
             values ( null
                    , username
                    , password(concat(username, salt, unencryptedpassword))
                    , description
                    , emailAddress
                    , createdById
                    , createdDate
                    , lastUpdatedById
                    , lastUpdatedDate );
  set userid = last_insert_id();
  commit;
end;
/

For my register.php page, I have the following:

<?php
$host="localhost";
$db="mydb";
$uname="myuser";
$pword="mypass";

$firstname=$_POST["firstname"];
$lastname=$_POST["lastname"];
$emailaddress=$_POST["emailaddress"];
$newpassword=$_POST["newpassword"];

$mysqli = new mysqli( $host, $uname, $pword, $db );
$res = $mysqli->multi_query( "call register(@userid,$emailaddress,$newpassword,$firstname,$emailaddress)" );
$mysqli->close();

$_SESSION["sessionId"] = 1;
?>

Problem is it never gets inserted into my database. Can anyone help me with this.

Thanks.

adshocker
  • 65
  • 8
  • Are you checking for errors using `mysqli->error()`? Also, surely using exactly the same salt for each user obviates the purpose of using a salt in the first place, you should randomly generate a different salt for each user. – Hammerite Mar 23 '11 at 14:47
  • Thanks for the tip. If I generate a random salt for each user, then I should save the salt to the table as well? – adshocker Mar 24 '11 at 12:10
  • I believe you would need to do that if you want to use the stored hashes, yes. – Hammerite Mar 24 '11 at 14:17

1 Answers1

1

Any reason you're using multi_query? You're only executing the one 'call' query. And it's chock full of SQL injection vulnerabilities, since you're not escaping any of those 4 values you pull from the _POST array.

mysql_multi_query returns boolean FALSE if the first query in the call fails. You should check $res for that:

$res = $mysqli->multi_query(...);
if ($res === FALSE) {
   die("Mysql error: " . $mysqli->error);
}
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • The query is supposed to be $res = $mysqli->multi_query( "call register(@userid,$emailaddress,$newpassword,$firstname,$emailaddress);select @userid" ); but for simplicity's sake I took it off for the moment. Tried to add $mysqli->error. It does give me an error. '@yahoo.com,123456,Adhocker,myemail@yahoo.com)' at line 1. Edited some of the values. – adshocker Mar 24 '11 at 11:22
  • Fixed the problem by changing it to this: register(@userid,'$emailaddress','$newpassword','$firstname','$emailaddress'). Thanks All. – adshocker Mar 24 '11 at 12:11
  • Putting single quotes around variables means PHP won't see them as variables. You should use double quotes instead. And for function calls like, that quotes are irrelevant. `func("$a")` and `func($a)` are identical, except the first one requires PHP to create a throwaway string which wastes CPU cycles. – Marc B Mar 24 '11 at 14:14