CODEWITHSUNDEEP
phpmysql
user892134
user892134

Reputation: 3224

PHP changing from mysql_real_escape_string to PDO prepared statements

I currently use mysql_real_escape_string to escape a variable when querying the database to prevent SQL injection. For example, 

   $keyword = mysql_real_escape_string($keyword);
        $guideline = mysql_real_escape_string($guideline);  
        mysql_query("INSERT INTO table1 VALUES('$keyword','$guideline')");

$get = mysql_query("SELECT * FROM table2 WHERE keyword='$keyword'");

  while($row = mysql_fetch_assoc($get)) {
  //code
  }

After reading about SQL injection prevention, i've read this isn't enough to stop SQL injection(so much code to go over now and correct) and i should be using PDO prepared statements? Can i have an example of how to do PDO prepared statements with the same $variables above?

Upvotes: 1

Views: 3156

Answers (4)

McCarvill
McCarvill

Reputation: 41

Here is what I do, not claiming to be a ninja, but have been at it for a while.

//connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

//get Post data
$name = filter_input(INPUT_POST, $name, FILTER_SANITIZE_STRING);

//SQL
$SQL = $conn->prepare('SELECT * FROM users WHERE user_name=:name;');
$SQL->execute(array(':name' => $name));

//While Loop
while($names = $SQL->fetch(PDO::FETCH_OBJ){
 echo $names->user_email
}

Upvotes: 0

FatalError
FatalError

Reputation: 964

This one makes more sense

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

http://www.php.net/manual/en/pdo.prepared-statements.php

Upvotes: 2

eggyal
eggyal

Reputation: 125855

First you must create a PDO object:

$dbh = new PDO("mysql:dbname=$dbname", $username, $password);

Then there are different ways to associate your parameters with your queries:

  1. As an argument to execute():

    $qry = $dbh->prepare("INSERT INTO table1 VALUES(?, ?)");
$qry->execute(array($keyword, $guideline));

  2. By binding values (retains value assigned at time of function call):

    $qry = $dbh->prepare("SELECT * FROM table2 WHERE keyword = ?");
$qry->bindValue(1, $keyword);
$qry->execute();

while ($row = $qry->fetch()) {
  // code
}

  3. By binding parameters (updates when underlying variable changes):

    $qry = $dbh->prepare("SELECT * FROM table2 WHERE keyword = ?");
$qry->bindParam(1, $keyword);
$qry->execute();

while ($row = $qry->fetch()) {
  // code
}

You can even use named placeholders instead of anonymous ?:

$qry = $dbh->prepare("SELECT * FROM table2 WHERE keyword = :kw");
$qry->bindValue(":kw", $keyword);

Upvotes: 3

prodigitalson
prodigitalson

Reputation: 60413

Its pretty simple really:

$db = new PDO($dsn, $user, $password);
$stmt = $db->prepare('INSERT INTO table1 VALUES(?,?)');
$stmt->execute(array($keyword, $guideline));
$stmt->close();

$stmt2 = $db->prepare('SELECT * FROM table2 WHERE keyword= ?');
$stmt->execute(array($keyword));
while(false !== ($row = $stmt->fetch())) {
   // do stuff
}

Note that you can also use named placeholders which can help make your code a bit more readable though a bit more verbose:

$stmt2 = $db->prepare('SELECT * FROM table2 WHERE keyword= :keyword');
$stmt2->execute(array(':keyword' => $keyword));

Upvotes: 4

Related Questions