[Phpwm] site critique please

[David Goodwin]

sukh virdee wrote :
> I always use *_escape_string.
> 
> Not sure what you mean by the term "prepared statements"... Can anyone 
> clear up the confusion?

I can try :-)

Effectively, you insert placeholders (often/normally ? marks) within an
SQL string. This is passed to the server which can then compile it.

Because you haven't (yet) passed any values into it, the server can
cache the compiled statement - saving it from having to do future work
This results in a performance boost if you run the same query multiple times.

When you do assign variables to the statement you've just created,
because the server knows where the variables are to be placed there is
no way that SQL injection can take place (the server will automatically
escape any quotes etc) (I don't know if it does actually escape quotes,
or if it's just that it doesn't need to).

As an example using mysqli (which I think has a horrible interface, but
i digress).

$mysqli = new mysqli($host, $user, $password, $database);
if(!$mysqli) { die mysqli_connection_error(); }

$sql = "SELECT id_number FROM dog WHERE name = ?";
$statement = $mysqli->prepare($sql);
$statement->bind_param('s', $search_term);
$search_term = 'bob';
if(!$statement->execute())) {
	die('Failed to execute query ' . $sql . ' - ' . $mysqli->error);
}
$statement->bind_result($id_number);
if($statement->fetch()) {
	echo 'Found : ' . $id_number;
}

(Sidenote, or why I don't like mysqli :
1) You can't do a 'select *' and have returned a useful array like
structure (that I can see anyway!)
2) The bind_param and bind_result are nasty (why can't I do
$stmt->bindString($search_term); ?)


MDB2 offers a nicer example :

$types = array("text");
$sql = "SELECT * FROM dish WHERE Description LIKE ?";
$statement = $con->prepare($sql, $types, MDB2_PREPARE_RESULT);
$data = array("%it's%");
$result_set = $statement->execute($data);
if(PEAR::isError($result_set)) {
    die("Failed to execute query: " . $result_set->getMessage();
}
$statement->free();
while($row = $result_set->fetchRow(MDB2_FETCHMOD_ASSOC)) {
    echo "Found : . $row["dish"] . "n";
}

(And MDB2 has the advantage; like Pear::DB, that it is cross database, and works with php4 and 5). (If a database doesn't support prepared statements, MDB2 will emulate them)


</diversion>

In either case you can see how ? marks are used within the sql string; a statement is prepared, and then stuffed with values.

I'm sure PDO offers the same functionality, I've just not yet used it.

David :)