mysql to mysqli, prepared statement

[helenp]

Hi,
Puf, exhausted.
Have just added sessions to my pay and booking pages, and then not to touch them more, I started to change mysql to mysqli.
I choosed mysqli and not pdo as I have I think hundred of pages maybe that needs to be updated, so I wanted the less changes in code as possible.

However found a problem when I was going to escape using mysqli_real_escape_string.
I used before for sql inyection but also to escape and insert ' in irish names etc.
It did not work, so I read I have to do prepared statements....
I been trying for hours,
but cant manage:

I have this so far:

<?
$mysqli = @new mysqli('localhost', 'xxxxx', 'xxx', 'table');
$mysqli->set_charset("utf8");

if ($mysqli->connect_error)
{
 die('There is a temporary problem with this page at this moment: ' . $mysqli->connect_error); 
}
include 'conexionmysqli.php'; 
$emailtrue = 'test';
$orderdatetime= '2014-01-21';
$statement = 'notpaid';
$query = $mysqli->prepare ("insert into orders (emailtrue, orderdatetime, statement) values (?, ?, ?)");
 /* Bind parameters
 s - String, b - Boolean, i - Integer etc, d - DOUBLE and FLOAT*/
$query->bind_param("sss", $emailtrue, $orderdatetime, $unpaid);
 $query->execute();
$query->close();

?>

This is really a pain when you have many parameters to insert in bind parameter.
Above does not work.
Also another two questions:
Can I use s for everything even when numbers are inserted?
Other things that is inserted such as now() for exampel, should that be included in the bind_param?
Thanks in advance.

[helenp]

There is an error with the parameters, I changed it into:
$emailtrue = 'test';
$orderdatetime= '2014-01-21';
$unpaid = 'notpaid';
$concepto = 'que';
but it does still not insert anything

[helenp]

Got it, best way or not, no idea,
didnīt know had to use stmp

$stmt = $mysqli->prepare ("insert into orders (emailtrue, orderdatetime, statement, concepto) values (?, ?, ?,?)");
 /* Bind parameters
 s - String, b - Boolean, i - Integer etc, d - DOUBLE and FLOAT*/
$stmt->bind_param("ssss", $emailtrue, $orderdatetime, $unpaid, $concepto);
$stmt->execute();

[helenp]

Btw, why is this forum slower and slower every day?

[penders]

didnīt know had to use stmp

Your first "non-working" example and your last "working" example appear to be the same, except you have a 4th column in your "working" example?

The only other difference, that you appear to hint at, is that you've used a different variable name - but you can call the variable anything you like (it contains a mysqli_stmt object regardless).

$myVar = $mysqli->prepare(....); 
$myVar->bind_param(....); 
$myVar->execute();

[swa66]

FWIW: prepare(), bind_param() and execute() can fail: you should check the return values.

It helps in debugging to print out the $mysqli->error in your logs (don't expose that to end-users they've no use for it and it will help attackers.)

[helenp]

Thanks all,
I didnt like the mysqli prepared statements, so I changed to do PDO wich I seem a lot cleaner and easier to work with.
This is my first code that works:

//prepared insert 
$emailtrue = 'tres';
$name='t';
$concepto='r'; 
$telhome='p'; 
$telmobile='k'; 
$holidaycost='l';
$sentencia = $dbh->prepare("INSERT INTO ordered (emailtrue, orderdatetime, statement, name, concepto, telhome, telmobile, holidaycost) VALUES (:emailtrue, NOW(), 'unpaid', :name, :concepto, :telhome, :telmobile, :holidaycost)");
$sentencia->bindParam(':emailtrue', $emailtrue);
$sentencia->bindParam(':name', $name);
$sentencia->bindParam(':concepto', $concepto);
$sentencia->bindParam(':telhome', $telhome);
$sentencia->bindParam(':telmobile', $telmobile);
$sentencia->bindParam(':holidaycost', $holidaycost);
// insertar una fila
$sentencia->execute();
 print $dbh->lastInsertId(); 
// create prepared statement and update
$id = $dbh->lastInsertId(); 
echo $id;
 $sql = $dbh->prepare ("UPDATE ordered SET id='pay$id' WHERE order_id = :id");
$sql->bindParam(':id', $id);
$sql->execute();

[edited by: helenp at 9:57 pm (utc) on Jan 26, 2014]

[helenp]

And this is a prepared statement for selecting, very clean, and short code:

$id='pay455';
$query = $dbh->query("
 SELECT id, order_id FROM ordered WHERE id = :id");
// fetch row
// execute statement, binding values to the parameters
$query->execute(array(
 'id' => $id
)); 
foreach ($query as $row) {
  echo html_escape('Post ' . $row['id'] . ' from ' . $row['order_id']) . '<br/>';
} 

[penders]

Yes, PDO would seem to be a better extension to work with, given the choice. Mysqli's bind_param() is a bit of a bind (pun intended) to say the least - particularly when it comes to dynamic queries and a variable number of parameters.