How to change from mysql to pdo using prepared statements in PHP?

Question

$dml = "insert into bookmark(accountId,category,url,hash,title,created) value($_SESSION[accountId],$_POST[category],'$_POST[url]',md5('$_POST[url]'),'$_POST[title]',now())";

mysql_query($dml,$con);

How do I do this statement using prepared statements in PDO?

If you wanted 'prepared statements', you had to ask for 'prepared statements'..... — Tyler Carter, Feb 06 '10 at 03:47
It just so happens that PDO is able to preform complete statements just as well as prepared statements. — Tyler Carter, Feb 06 '10 at 03:48
People use PDO to be able to use it across multiple types of databases, as PDO is not MySQL dependent. If people wanted to just use prepared statements and didn't care about platform independent stuff, they could use MySQLi. Therefore, if you don't state that you wanted prepared statements, I can't infer it. — Tyler Carter, Feb 06 '10 at 03:50
Yes. You need to use the Object Oriented form of it, but MySQLi can do prepared statements. — Tyler Carter, Feb 06 '10 at 04:09
This post mentioned mysqli cant do this job:http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons — user198729, Feb 06 '10 at 04:13
mysqli can do it, but it's a little clunkier. You have to bind columns to variables with `mysqli_stmt::bind_result` before fetching rows, whereas `PDOStatement::fetch` can return arrays & objects. — outis, Feb 06 '10 at 04:28
Note that the procedural interface of mysqli also supports prepared statements: `mysqli_prepare` (http://php.net/mysqli_prepare), `mysqli_stmt_bind_param` (http://php.net/manual/en/mysqli-stmt.bind-param.php), `mysqli_stmt_execute` (http://php.net/manual/en/mysqli-stmt.execute.php) &c. — outis, Feb 06 '10 at 04:41
Another benefit of prepared statements is you won't be a victim of SQL injection — axsuul, Feb 06 '10 at 06:13
Well, if you know how to properly escape your data, you won't be a victim of it either... — Tyler Carter, Feb 06 '10 at 06:24

score 3 · Answer 1 · answered Feb 06 '10 at 04:04

$dml = $db->prepare("INSERT INTO bookmark (accountId, category, url, hash, title, created) VALUES (:account_id, :category, :url, MD5(:url), :title, NOW());");

$dml->bindParam(':account_id', $_SESSION['accountId']);
$dml->bindParam(':category', $_POST['category']);
$dml->bindParam(':url', $_POST['url']);
$dml->bindParam(':title', $_POST['title']);

$dml->execute();

score 3 · Accepted Answer · answered Feb 06 '10 at 04:05

3

$dml = "INSERT INTO bookmark (accountId, category, url, hash, title, created) "
    . "VALUES (:accountId, :category, :url, MD5(:url), :title, NOW())";
$statement = $pdo->prepare($dml);
$parameters = array(
    ":accountId" => $_SESSION["accountId"],
    ":category" => $_POST["category"],
    ":url" => $_POST["url"],
    ":title" => $_POST["title"]);
$statement->execute($parameters);

answered Feb 06 '10 at 04:05

Adrian

1,392
9
9

PDO will handle escape and quoting as needed. – acrosman Feb 06 '10 at 04:27
@user198729: quoting is only necessary if the underlying driver has to simulate prepared statements. Generally speaking, values are sent separate from the statement, so there's no confusion between where a value ends and the rest of the statement begins. – outis Feb 06 '10 at 04:31
Can you be more specific about when is quoting necessary? – user198729 Feb 06 '10 at 04:34
@user198729: quoting is never necessary for you when using parameters. I should have made this clearer, but my previous comment was referring solely to the underlying DB drivers that implement the PDO interface. That said, only part of SQL statements can be parameterized: parts where an atomic value is to be substituted. Names (column, table, DB), clauses, lists (such as the second argument to `IN`) &c can't be parameterized. If any of these needs to vary, you'll need to construct the statement, which means you'll be responsible for sanitizing any user input. – outis Feb 06 '10 at 04:48

How to change from mysql to pdo using prepared statements in PHP?

2 Answers2

Linked