How to use/write mysql_real_escape_string in PDO?

Question

In my code Im trying to translate mysql_real_escape_string into a PDO statement. Does somebody have a tip on how to write mysql_real_escape_string in PDO?

Im using mysql_real_escape_string in two lines: $userName = mysql_real_escape_string($_POST['username']); $password = sha1(mysql_real_escape_string($_POST['password']));

<?php
ob_start();
session_start();
include("../database/db.php");

$userName = mysql_real_escape_string($_POST['username']);
$password = sha1(mysql_real_escape_string($_POST['password']));
echo "<br>user: " . $userName;
echo "<br> pw: " . $password;

$query = "select * from tbladmin where admin_usr_name='$userName' and admin_pwd='$password'";

$res = mysql_query($query);

// $rows = $res->fetch(PDO::FETCH_ASSOC); 
$rows = mysql_fetch_assoc($res);
echo "<br>numrows" . mysql_num_rows($res) . "<br>";

// $find = $query->prepare("select * from tbladmin where admin_usr_name='$userName' and admin_pwd='$password'");
// $find->execute();
// if ($find->fetchColumn() > 0)
// {
// echo 'You made it, welcome';
//  $_SESSION['userName']  =  $rows['admin_usr_name'];
//  $_SESSION['admin_id'] = $rows['admin_id'];
//  header("location: ../pages/content.php");
// }
// else
// {
//  echo 'Username and password dont match <br/> Try again';
//  header("location: ../index.php?loginerror=yes");
// } 

if(mysql_num_rows($res)>0)
{
    $_SESSION['userName']  =  $rows['admin_usr_name'];
    $_SESSION['admin_id'] = $rows['admin_id'];
    header("location: ../pages/content.php");
}
else
{
    echo 'Username and password dont match <br/> Try again';
    header("location: ../index.php?loginerror=yes");
}

?>

This is what Im trying to do with PDO

  $host = "localhost"; 
$user = "root"; 
$password = "root";
$db = "blog";

$dsn = "mysql:host=$host;dbname=$db;charset=utf8";
$opt = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
$pdo = new PDO($dsn,$user,$password, $opt);

$username = $_POST['username'];
$password = $_POST['password'];
$query = "select * from tbladmin where admin_usr_name=:userName and admin_pwd=:passWord";

try 
{   
$databas = new PDO($dsn, $user, $password);
} 
catch (PDOException $e) 
{
echo 'Connection failed: ' . $e->getMessage();
}

$statement = $databas->prepare($query);

$statement->execute(array(':userName'=>$username, ':passWord'=> $password)); 
$row = $statement->fetch();

I always get this error: Call to a member function prepare() on a non-object

You don't need to. There is no need for escaping with prepared statements. — Oliver Charlesworth, Mar 26 '13 at 22:04
You don't use escape functions when using prepared statements. You *bind* the parameters. See the documentation: http://www.php.net/manual/en/pdostatement.bindparam.php — Michael, Mar 26 '13 at 22:04
Sometimes there is need to, because table names, for example, cannot be dynamic, in PDO prepared statements. — Rolf, Oct 20 '18 at 08:37

Peter Featherstone · Answer 1 · 2013-03-26T23:18:10.330

8

The point is by using prepared statements with parameterised queries and bound values you don't need things such as mysql_real_escape_string.

Check out the PDO documentation on how to use bound values and parameterised queries/prepared statements.

The point is that you would write an SQL query as such:

$query = $pdo->prepare("SELECT * FROM users WHERE username = ? and password = ?");

You would then pass in the bound values in place of the ? symbols, thus the query is only ever run literally as so:

$query->bindParam(1, $username);
$query->bindParam(2, $password);

Any sort of SQL injection attempt such as 1'; DROP users -- inside the variables above would no longer work as it would be called literally.

edited Mar 26 '13 at 23:18

answered Mar 26 '13 at 22:04

Peter Featherstone

7,835
4
32
64

Thanks, nice answer. Im gonna take a look in the PDO documentation. – Bruno Chavez Mar 26 '13 at 22:10
Once you have used PDO for running your database queries you will feel a whole lot safer in what you are doing. If you need any help with it don't hesitate to let me know! – Peter Featherstone Mar 26 '13 at 22:11

score 4 · Answer 2 · answered Mar 26 '13 at 22:09

4

You don't need to in PDO. At least, rarely do you need to.

Use parameterized SQL.
Bind your parameters.
PDO::QUOTE automatically escapes its strings.

answered Mar 26 '13 at 22:09

cygorx

228
1
2
19

Thanks, nice links... – Bruno Chavez Mar 26 '13 at 22:14
When do you actually need PDO::QUOTE? – bestprogrammerintheworld Mar 26 '13 at 22:32
@bestprogrammerintheworld sometimes there will be text that lacks proper string format. – cygorx Mar 26 '13 at 22:33
@cygorx, yes BUT you won't have to when using prepared statements and it's strongly recommended not to use PDO::QUOTE... – bestprogrammerintheworld Mar 26 '13 at 22:35
@bestprogrammerintheworld I put it as an option, not a fundamental use. I provided the first two links as a static reference. – cygorx Mar 26 '13 at 22:39
1

@cygorx - I understand. My opinion is that it shouldn't even be an option though :-) – bestprogrammerintheworld Mar 26 '13 at 22:42
You never know, sometimes the most profoundly grievance can be the only option. – cygorx Mar 26 '13 at 22:43
@cygorx - I agree :-) – bestprogrammerintheworld Mar 26 '13 at 23:11

score 1 · Answer 3 · edited May 23 '17 at 10:32

1

As stated before, you don't have to use escaping... because this is handled more securely by binding values with ? or naming with colons in the sql-statement

I would do something like this:

<?php
$username = $_POST['username'];
$password = $_POST['password'];
$query = "select * from tbladmin where admin_usr_name=:userName and admin_pwd=:passWord";

try {   
  $db = new PDO($dsn, $un, $pw);
} catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
}

$statement = $db->prepare($query);

//:userName and :passWord is set when actually executing the query
$statement->execute(array(':userName'=>$username, ':passWord'=> $password)); 
$row = $statement->fetch();
?>

Notice that fetch() will only return one row (next row in resultset). If you want to access the whole recordset you can do this in 2 ways:

Loop through resultset with while() { } OR
Use $rows = $statement->fetchAll(); and loop through the array $rows

Use option 1 together with larger resultsets.

Use option 2 with minor resultsets (and you know it will not grow so much)

Take in consideration whether or not you should set attribute of PDO::ATTR_EMULATE_PREPARES. More info about this here: PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

edited May 23 '17 at 10:32

Community

1
1

answered Mar 26 '13 at 22:27

bestprogrammerintheworld

5,417
7
43
72

@bestprogrammerintheword Thanks, Im gonna try your code. – Bruno Chavez Mar 26 '13 at 22:35
You're welcome. Feel free to ask if you got stuck. – bestprogrammerintheworld Mar 26 '13 at 22:37
When I run your code i get this error message: Fatal error: Call to a member function prepare() on a non-object. And I wonder what is $db in your code? – Bruno Chavez Mar 26 '13 at 22:54
$db wasn't assigned in my code. I added some rows to illustrate. $db is the PDO-object. $dsn is the host and dbname and $un is the username to connect to the database, and $pw is the password to connect to the database. Look at http://www.php.net/manual/en/pdo.construct.php for more info about creating the PDO-object. – bestprogrammerintheworld Mar 26 '13 at 23:10
@estprogrammerintheworld Thanks, but I stil get this error: Call to a member function prepare() on a non-object – Bruno Chavez Mar 27 '13 at 13:19
Can you show your code again after you done the changes I suggested? (edit your question or give a link etc) – bestprogrammerintheworld Mar 27 '13 at 17:21

How to use/write mysql_real_escape_string in PDO?

3 Answers3