What is the equivalent of these mysql functions in pdo?

Question

I have a php script that contain many mysql functions na connection to database , and for security reasons to prevent SQl injection I would like to replace these mysql functions with pdo functions.

$e = mysql_real_escape_string($l);

$query = "SELECT * FROM users";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result)

$query = "SELECT * FROM users WHERE (first_name='" . $f_name . "' AND last_name='" . $l_name . "') OR (email = '" . $email . "')";
    $result = mysql_query($query);
    $row = mysql_fetch_assoc($result);

$insert = "INSERT INTO users(`email`, `first_name`, `last_name`, `created_at`, `modified_at`) VALUES ('{$email}', '{$f_name}', '{$l_name}','" . date('Y-m-d H:i:s') . "','" . date('Y-m-d H:i:s') . "');";

$update = "UPDATE users SET `is_user`=1 WHERE user_id=" . $user_id;

mysql_query($insert) or die(mysql_error());

mysql_query($update) or die(mysql_error());

PDO in itself doesn't protect any more than other APIs - you need to bind the variables to protect against attacks, and other APIs offer that too. That being said, this isn't a code-converting service - read the manual for [`PDO::prepare()`](http://php.net/pdo.prepare) and [`PDOStatement::bindParam()`](http://php.net/pdostatement.bindparam) to get started. — Qirel, Oct 02 '17 at 09:11
Use prepared statements or Stored procedures also google something about security, starting by google is going to learn you alot. There are alot of online sql security books etc to find — Tomm, Oct 02 '17 at 09:14
@Qirel I know prepare() function and execute() , I'm talking about the queries and the other functions like mysql_fetch_assoc($result) — Sam, Oct 02 '17 at 09:31
Here you go: [Getting data out of statement. fetch()](https://phpdelusions.net/pdo#fetch) — Your Common Sense, Oct 02 '17 at 09:33
@Sam If you have a look at the manual for `prepare()`, there are examples that show how to fetch data. Instead of `fetchAll()` you can loop over `fetch()`, see the manual for that method. — Qirel, Oct 02 '17 at 09:49

score 0 · Answer 1 · answered Oct 02 '17 at 09:42

This is a clean version you can use to do PDO :

<?php
    $cnx = new PDO('mysql:host=HOST;dbname=DATABASE','userid','passeword');
    $cnx->query('SET NAMES utf8');
    $cnx->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

    try{
        $req = $cnx->prepare("SELECT * FROM users WHERE (first_name=:firstname AND last_name=:lastname) OR (email=:mail);");
        $req-> bindValue(':firstname',$f_name);
        $req-> bindValue(':lastname',$l_name);
        $req-> bindValue(':mail',$email);
        $req-> execute();

        $data = $req->fetchAll(PDO::FETCH_COLUMN);

    }
    catch(PDOExeption $e){} 
?>

According to what you need, see the difference between : PDO::FETCH_FUNC / PDO::FETCH_COLUMN / PDO::FETCH_GROUP

fetchAll doc :
http://php.net/manual/fr/pdostatement.fetchall.php

You can bind all your values in one line too, see the binValue documentation.

There is nothing clean in this code, it has numerous critical faults. given you are struggling with most basic syntax yourself, it is not a good idea to answer other questions. — Your Common Sense, Oct 02 '17 at 09:45

What is the equivalent of these mysql functions in pdo?

1 Answers1