Help me format this php PDO statement properly

[cunoodle2]

It is not inserting and I'm not familiar enough with the PDO stuff to even get an error to print to the screen.

 

<?php
		$statement = "INSERT INTO `User` (`Email`,`Password`, `Firstname`, `LastIP`, `OrginIP`, `DateSignUp`, `LastLogin`) VALUES ";
		$statement .= "(?, ?, ?, ?, ?, NOW(), NOW());";

		$stmt = $write->prepare($statement);
		$user_info = $stmt->execute(array($email, $password, $firstname, $ip, $ip));
?>

 

Any help would be GREATLY appreciated.

[chaking]

pretty sure you have to bind those question marks to some values as so:

 

<?php
$write = new mysqli($host, $user, $passwd, $dbname);
$statement = "INSERT INTO `User` (`Email`,`Password`, `Firstname`, `LastIP`, `OrginIP`, `DateSignUp`, `LastLogin`) VALUES ";
$statement .= "(?, ?, ?, ?, ?, NOW(), NOW())";
$stmt = $write->prepare($statement);
$stmt->bind_param("sssss", $email, $password, $firstname, $ip, $ip);
$stmt->execute();
echo $stmt->affected_rows . " records inserted<br />\n";
$stmt->close();
?>

 

Something like that should do the trick

Make sure to tell it what type of value is going in... the "sssss" that means all strings... if the second value was an integer it would be "sissss"...

 

Also be sure to take out that double semi-colon at the end of $statement

 

Kudos for using sql-injection-proof statements

 

 

[cunoodle2]

pretty sure you have to bind those question marks to some values as so:

I never had to bind anything before.  I've done other inserts and for some reason they worked without fault.  Additionally this statement actually worked until I added the two $ip fields.  I'm wondering if it has something to do with that.

 

Also be sure to take out that double semi-colon at the end of $statement

I don't think there is a double semi-colon.  The one at the very end is the end of the php line of code.  The other is actually part of the query which is sent to the execute statement and marks the end of the query itself.  Or am I totally off here?

 

Kudos for using sql-injection-proof statements

Thanks.  Its actually quite a bit of work but I'm working on a bigger site (my own thing) and I am now countess hours into just the user registration page as I've written so many clean/error checking functions its not even funny.

 

I'm still unable to get this insert to work for some reason.  Does anyone know why?  How can I print errors to the screen with PDO??  Sorry this is all new to me.

[Axeia]

You do have to set the questionmarks to a value somewhere, and suprisingly enough they start at 1 instead of 0.

So

$stmt->bindParam( 1, $email );
$stmt->bindParam( 2, $password );
$stmt->bindParam( 3, $firstname );
$stmt->bindParam( 4, $lastIp );
$stmt->bindParam( 5, $originIp );
$stmt->bindParam( 6, $signup );
$stmt->bindParam( 7, $lastlogin );

 

For the errorcode have a look at:

http://nl3.php.net/manual/en/pdostatement.errorcode.php

http://nl3.php.net/manual/en/pdostatement.errorinfo.php

[cunoodle2]

I did this...

<?php
        $stmt = $write->prepare($statement);
        $user_info = $stmt->execute(array($email, $password, $firstname, $ip, $ip));
echo "\nPDOStatement::errorCode(): ";
print $user_info->errorCode();
?>

And on the screen it printed..

PDOStatement::errorCode():

Fatal error: Call to a member function errorCode() on a non-object in /home/website/public_html/NewUser.php on line 112

 

Also you do not need to bind parameters (I haven't done it in any of my other PDO statements and they are all working fine).  Here is a quote (from dev.mysql.com/tech-resources/articles/mysql-pdo.html)...

 

There are a number of different ways of using the PDOStatement class. You do not need to bind parameters. The code commented out in the preceding listing shows that an array of values can be passed directly to the PDOStatement::execute method, bypassing the need to bind parameters. It is also possible to use names rather than question marks as place holders for the replaceable parameters in an SQL statement.

[chaking]

From my understanding, there's little use for the question mark if you aren't going to bind anything...

 

Running the variables through the execute function takes away the injection protection... <- neat word, injection protection

[Daniel0]

Maybe if all of you would bother to use the manual for its intended purposes it would all become clear:

 

http://php.net/manual/en/pdostatement.execute.php

 

Running the variables through the execute function takes away the injection protection... <- neat word, injection protection

No.

[chaking]

harsh

[Daniel0]

harsh

 

Yet better than posting false information I would argue.

 

And on the screen it printed..

PDOStatement::errorCode():

Fatal error: Call to a member function errorCode() on a non-object in /home/website/public_html/NewUser.php on line 112

 

If you would read the manual (sorry, chaking) you would see that PDOStatement::execute() returns a boolean value. Scalar values do not have any methods, only objects do. Further, if you read the pages Axeia linked to you would see that errorCode() is a member of PDOStatement, so it's $stmt->errorCode() and not $user_info->errorCode().

[chaking]

Anyway...

http://us2.php.net/manual/en/pdo.prepare.php the first comment there is what I was basing the injection information on...

[Daniel0]

Well, admin at wdfa dot co dot uk is only partially correct. He is not doing much else than stating the obvious. If you have $var = "User', email='test"; then his string passed to PDO::prepare() evaluates to UPDATE `users` SET user='User', email='test' because string variable interpolation takes place before the string is passed as argument to a function or method. Again, that is covered in the manual: http://php.net/manual/en/language.types.string.php

 

The manual entry for PDOStatement::execute() explicitly says that you can either pass an array or bind them manually using PDOStatement::bindParam().

 

Do remember that the comments can be submitted by anyone and the comments are not an authority. The manual entries on the other hand are authoritative.

[chaking]

Way off topic, so I'll just finish with saying:

 

Yes, the manual says you can pass an array or bind them... but that's not the point, the point was whether there was any inherent sql injection protection when passing an array to execute. As the poster noted, there wasn't.  However, if binded, the value would have been escaped and all would be good...

 

Yes, I'm quite aware that posters on the php site aren't authorities. Thanks for the reminder though :-/

 

Sorry for being off topic...

[Daniel0]

And my point is that statement is incorrect. Using his example:

<?php
$var="User', email='test";
$a=new PDO("mysql:host=localhost;dbname=database;","root","");
$b=$a->prepare("UPDATE `users` SET user=:var");
$b->bindParam(":var",$var);
$b->execute();
?>

is the exact equivalent of

<?php
$var="User', email='test";
$a=new PDO("mysql:host=localhost;dbname=database;","root","");
$b=$a->prepare("UPDATE `users` SET user=:var");
$b->execute(array(':var' => $var));
?>

 

You can test it yourself if you want to.

[cunoodle2]

Daniel0 IS THE MAN!!!

 

I seriously sat here for over 24 hours looking and looking at this code and could not figure out what in the world I was doing wrong.  Daniel0 and I agree (sorry chaking and Axeia) that you absolutely do NOT have to bind your variables in a PDO statement.  The following is 100% working code with absolutely no binding at all.  After my code I'll explain what I was doing wrong above that was forcing the lack of insert.

 

<?php
$write = new PDO('mysql:host=localhost;dbname=db_name', 'db_user', 'db_pass');
$query = "INSERT INTO `User` (`Email`,`Password`, `Firstname`, `DateSignUp`, `OrginIP`, `LastLogin`, `LastIP`) VALUES ";
$query .= "(?, ?, ?, NOW(), ?, NOW(), ?);";
$stmt = $write->prepare($query);

if ($stmt->execute(array($email, $password, $firstname, $ip, $ip)))
	echo "SUCCESS AGAIN!!<br />\n";

else
{
	echo "\nPDOStatement::errorCode(): ";
	print $stmt->errorCode();
}
?>

 

You ready for MY stupid mistake that caused me 24 hours+ of stress?  For some reason when I set up the table I had mistakenly marked the "password" field as "UNIQUE."  I hadn't finished all my encryption stuff as of yet and was simply using "123" as my testing passwords.  Since there was already a "123" in the password field it would not insert any more. 

 

Quick clarification before someone on here says that I should never use 123 as a password or I should encrypt... Again I'm in the VERY early testing phases and would obviously never use 123 as an actual password.  I haven't finished all of my encryption stuff yet and was just trying to get the basics of the PDO stuff in order.

 

I've learned a lot and want to thank Daniel0 for clearing up how execute() and errorCode() actually function.  This is all new stuff to me.  Also chaking I do appreciate your help as well and keep writing all your stuff PDO style.  It's the only way to go!!!!!