PHP PDO: how to escape if I pass a query with variable parameters?

Question

I'm trying to upgrade my old code with the new PDO code.

I used to have a generic function DoQuery($query); in my main Class, say myclass, so i can call it simply from any page like

$q = "select * from table where name = 'john' "
$result = $myclass->DoQuery($q); // returns an array i can loop

my current code is

function DoQuery($query, $assoc=0){

        try {
            $pdo = new PDO('mysql:host='.$this->sql["db_host"].';dbname='.$this->sql["db_data"], $this->sql["db_login"], $this->sql["db_pass"]);        
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $stmt = $pdo->prepare($query); 
            $stmt->execute(); 
        } catch(Exception $e) {//print error }

        if ($assoc){
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); 
        }  else{
            $rows = $stmt->fetchAll(PDO::FETCH_NUM); 
        } 
        return $rows;
}

so i don't understand how do i safely escape the inputs if i have variable parameters (like 'name' in this case but can be whatever)?

Pass the value into the `execute` as an array and use a question mark as a placeholder in the query itself. — chris85, Mar 30 '15 at 03:31
what does it mean? which value? how do i know everytime which is the value? can be any query... — Francesco, Mar 30 '15 at 04:03

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

Unless I've misinteruptted your questions this should do it..

$q = "select * from table where name = ?";
$params = array('john');
$result = $myclass->DoQuery($q, 0, $params); // returns an array i can loop

and then update your function...

function DoQuery($query, $assoc=0, $params=array()){
   $pdo = new PDO('mysql:host='.$this->sql["db_host"].';dbname='.$this->sql["db_data"], $this->sql["db_login"], $this->sql["db_pass"]);        
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $stmt = $pdo->prepare($query); 
    $stmt->execute($params);
    } catch(Exception $e) {//print error }
    if ($assoc){
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); 
    }  else{
        $rows = $stmt->fetchAll(PDO::FETCH_NUM); 
    } 
    return $rows;
}

Here's another thread on the topic. PHP PDO prepared statements

edited May 23 '17 at 11:50

Community

1
1

answered Mar 30 '15 at 04:14

chris85

23,846
7
34
51

omg, thank you so much. yes. yes. that is what i meant. I don't understand why people give me negative votes... If I were to formulate my question better, probably I were able to answer my self, no? ;) – Francesco Mar 30 '15 at 04:39
so is that enough to sanitize inputs? prevent SQLInjections etc? right? – Francesco Mar 30 '15 at 04:39
Yup, this is the way to sanitize. I'm not sure about the down voting either, or the down voting without commenting. – chris85 Mar 30 '15 at 12:05

PHP PDO: how to escape if I pass a query with variable parameters?

1 Answers1