Does php mysqli or pdo wrapper class help to prevent sql injection?

Question

If we are using php mysqli or PDO wrapper class, do they prevent SQL injection risks ?

E.g.

https://github.com/ezSQL/ezSQL

or

https://github.com/bennettstone/simple-mysqli

Always Use prepared statements and parameterized queries is advised by all experts.

wrapper class is useful for less coding in quick time and also help to reduce repeated coding.

Then how can we use wrapper class along with prepared statements and parameterized queries simultaneously ?

I am confused by this?

e.g.

example from - How can I prevent SQL injection in PHP?

Use prepared statements and parameterized queries

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// do something with $row
}

And with wrapper class, e.g. we use it as

$name = $database->filter($_POST['name']);
$query = $database->get_results("select* from employee where name='$name'");
foreach ($query as $row){
// do something with $row
}

Then where to use wrapper and where to use prepared statements ?

how to use both simultaneously ?

How to achieve sql injection prevention while using wrapper class ?

Its the prepared and parameterized queries that provide the SQL Injection protection. Not purely using `mysqli_` or `PDO` — RiggsFolly, Apr 20 '17 at 14:37
*"I am confused by this?"* - So don't use it. Use what's already in place on php.net — Funk Forty Niner, Apr 20 '17 at 14:40
I think what @Fred-ii- is saying there is the wrapper is in no way necessary, and the use of it does not necessarily automatically provide protection from SQL Injection — RiggsFolly, Apr 20 '17 at 14:41
@RiggsFolly This mean I should use prepared statements always...instead of wrapper class ? Because through wrapper class and through prepared statement, we use common queries like insert, update, delete etc... — Dr Manish Lataa-Manohar Joshi, Apr 20 '17 at 14:45
You can make simple queries without statement. You can also set Vaariables and run a prepared statement afterward. There is alot of possible way to use mysqli with or without prepared statement, i have an answer ready but the thread is closed. — Louis Loudog Trottier, Apr 20 '17 at 14:58
@RiggsFolly Please read my edited question and also please read comment by Louis Loudog Trottier — Dr Manish Lataa-Manohar Joshi, Apr 20 '17 at 15:01
See [A Hitch Hickers Guide to SQL Injection](https://phpdelusions.net/sql_injection) I am not sure you actully understand what it is and this may help you to do that — RiggsFolly, Apr 20 '17 at 15:17

score 0 · Answer 1 · edited May 23 '17 at 12:09

Combining parameterization with a wrapper class would be like if you make your wrapper's get_results() method take an optional array of params:

get_results($sql, array $params = null)

Then the code for that function would use bind_param() with the array. But it's a pain in Mysqli because bind_param() takes varargs. It's ugly. You have to make the array into an array of references, and then use call_user_func_array() to pass the array as varargs to mysqli's bind_param().

I have a solution here: https://stackoverflow.com/a/7383439/20860

But I urge you to use PDO instead. It's much easier for this task because you can just pass your array of params to PDOStatement::execute().

Something like this:

class MyDatabaseWrapper {

    protected $pdo;

    ...

    public function get_results($sql, array $params=null) {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

}

it's better to return a statement. much more flexible and also error-proof — Your Common Sense, Apr 20 '17 at 15:15

Your Common Sense · Answer 2 · 2017-04-20T15:14:25.337

The answer is simple: a good wrapper class always lets you use prepared statements. Otherwise simply don't use it.

Then where to use wrapper and where to use prepared statements ?

Always.

how to use both simultaneously ?

Just use them.

How to achieve sql injection prevention while using wrapper class ?

By using prepared statements offered by a wrapper class.

It means that both outdated wrappers you managed to find so far should never be used. There are other wrappers that offer you both simplicity and safety.

For example, I've got a very simple PDO wrapper. It's just few lines thanks to PDO already being a wrapper, offers you a lot of automation out of the box.

It makes your code even simpler than with those failed wrappers you mentioned:

$query = $database->run("select* from employee where name=?", [$_POST['name']]);
foreach ($query as $row){
// do something with $row
}

Does php mysqli or pdo wrapper class help to prevent sql injection?

2 Answers2