Prepared statements

[chokk]

Hey all,What is the difference between mysqli::prepare and mysqli::query? Can they both be used to separate SQL logic from user input?

[justsomeguy]

If you use the query method only it's still your responsibility to escape all data.

[Ingolme]

Their purpose is not to separate SQL from user input. They are simply mechanisms to query the MySQL server."prepare" will prepare a query with some spaces reserved for data. A prepared statement lets you use a same query with different values easier than a normal query. I haven't looked much into it, but I believe it also will escape the information for you when using values in the query which makes it safer than normal queries.

[chokk]

I thought one of the purposes of prepared statements was that you didn't have to escape user input?

[Ingolme]

I thought one of the purposes of prepared statements was that you didn't have to escape user input?

That's correct. But if you only use mysqli_query() you'll have to escape the information yourself, which is what justsomeguy was referring to.

[chokk]

Cool, thanks! Is the following a correct prepared statement and query?

$mysqli = new mysqli($host, $user, $name, $db);$result = $mysqli->prepare("SELECT * FROM table_name WHERE id=?");$result->bind_param("i", $input);$result->execute();$result->bind_result($var1, $var2, $var3);$result->fetch();$result->close();

[chokk]

I have problems with the following snippet of code:

	function slet($person){	$mysqli = $this->connect;	$mysqli->connect();	$mysqli->prepare("DELETE FROM personer WHERE id = ?");	$mysqli->bind_param("i", $person);	$mysqli->execute();	$mysqli->close();}

Getting this error:

Fatal error: Call to undefined method mysqli::bind_param() in C:\wamp\www\...\...\sysadm.php on line 232

[justsomeguy]

The bind_param method is defined on the mysqli_stmt class, not mysqli. The prepare method returns a mysqli_stmt object.http://www.php.net/manual/en/mysqli-stmt.bind-param.php

[chokk]

So how would the previous statement be done properly? I have a hard time understanding the link you provided. Do I have to instantiate a new mysqli_stmt object or what?

[birbal]

$pst=$mysqli->prepare("DELETE FROM personer WHERE id = ?");$pst->bind_param("i", $person);

it will return a mysqli_stmt object which you have to cacth it in a variable eg. $pstnow you can call the method bind_param() of $pst object which is instance of mysqli_stmt class

[chokk]

Aha, now I get it!Thanks for the help to y'all I'll return the favor some day hopefully ;p