Converting regular mysql into prepared statements

Question

Im new to database and i have written a LOT of PHP code that accesses a database using MySQL.

I didnt take into account SQL injection attacks so i have to re-write all that PHP code to use mysql prepared statements.

After looking at videos on how to used prepared SQL statements, to perform just ONE SQL command requires a whole lot of "prepared" statements. My existing code has lots of different SQL statements all over the place, it would be a nightmare to change all that code to pack and unpack all the required preparation for each "prepared" statement command.

Is there some kind of wrapper i can use to prevent turning one line of regular SQL into 6 or 7 lines of prepared statements?

For example use to do this line line of SQL

SELECT * from users where userid=10

needs many more lines of prepared SQL statements, especially if there are lots of other SQL statements too it now becomes very complex.

Is there was some sort of one line wrapper that i can call that accepts the template SQL string, plus the parameters, which also executes the command and returns the result in just one line of wrapper for different types of MYSQL statements it would be great and the code would be much less confusing looking and error prone.

For example

 $users=WrapAndExecute($db,"SELECT * from users where userid=?","s",$userid);

 $data=WrapAndExecute($db,"UPDATE table SET username=?,city=?","ss",$name,$city);

 $result=WrapAndExecute($db,"DELETE from table where id=?","s",$userid);

 $result=WrapAndExecute($db,"INSERT into ? (name,address) VALUES(?,?)","ss","users",$name,$address);

Each of those lines above would create a prepared statement template, do the bind, execute it and return the result that a regular MYSQL statement would. This would create minimal impact on existing code.

Anybody knows how to do this or if some easy php library or class already exists to do this, that i can just import and start using it?

Thanks

Answer 1

You don't need to change a query to a prepared statement if it has no PHP variables in it. If it has just constant expressions, it's safe from SQL injection.

$sql = "SELECT * from users where userid=10"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

You don't need to change a query that contains PHP variables, as long as the value of that variable is a constant specified in your code. If it doesn't take its value from any external source, it's safe.

$uid = 10;
$sql = "SELECT * from users where userid=$uid"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

You don't need to change a query that contains PHP variables, as long as you can filter the value to guarantee that it won't risk an SQL injection. A quick and easy way to do this is to cast it to an integer (if it's supposed to be an integer).

$uid = (int) $_GET['uid'];
$sql = "SELECT * from users where userid=$uid"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

That leaves cases where you are using "untrusted" values, which may have originated from user input, or reading a file, or even reading from the database. In those cases, parameters are the most reliable way to protect yourself. It's pretty easy:

$sql = "SELECT * from users where userid=?"; // Safe!

// two lines instead of the one line query()
$stmt = $pdo->prepare($sql);
$stmt->execute([$_GET['uid']]);

$data = $stmt->fetchAll();

In a subset of cases, you need one additional line of code than you would normally use.

So quit your whining! ;-)

---

Re your comment about doing prepared statements in mysqli.

The way they bind variables is harder to use than PDO. I don't like the examples given in http://php.net/manual/en/mysqli.prepare.php

Here's an easier way with mysqli:

$sql = "SELECT * from users where userid=?"; // Safe!

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $_GET['uid']);
$stmt->execute();
$result = $stmt->get_result();

$data = $result->fetch_all();

I don't like the stuff they do in their examples with bind_result(), that's confusing and unnecessary. Just use get_result(). So with mysqli, you need two more lines of code than you would with PDO.

I've written query wrappers for mysqli that emulate the convenience of PDO's execute() function. It's a PITA to get an array mapped to the variable-arguments style of bind_param().

See the solution in my answers to https://stackoverflow.com/a/15933696/20860 or https://stackoverflow.com/a/7383439/20860

Answer 2

I were in the same boat, and I wrote such a wrapper that works exactly the way you want, save for it's being a class, not a function.

$user = $sdb->getRow("SELECT * from users where userid=?s", $userid);
$sdb->query("UPDATE table SET username=?s, city=?s", $name, $city);
$sdb->query("DELETE from table where id=?s", $userid);
$sdb->query("INSERT into ?n (name,address) VALUES(?s,?s)","users", $name, $address);

The above is a working code, as long as you have somewhere in your bootstrap file

$db = mysqli_connect(...);
...
require 'safemysql.class.php';
$sdb = new SafeMySQL('mysqli' => $db);

Note that none of the other suggestions could do anything like that.

Also note that if I were writing it today, I would have used PDO, as this class is duplicating a lot of functionality already exists in PDO.

Answer 3

Take a look at the PDO extension in PHP - http://php.net/manual/en/intro.pdo.php: it it secure against injections thanks to prepared statements; also, it allows you to connect to many different databases (e.g. MySQL, MSSQL, etc.).

You can then build your own wrapper as you wish to keep it clean; for example your own wrapper could be as follows: (following example will return user rows as objects)

// connect to DB
$GLOBALS['default_db'] = new DB('localhost','db_name','username','password') ;

// Get users and output results
$query = new DBQuery('SELECT * FROM users WHERE userid = ?',array(10)) ;
var_dump($query -> results()) ;
var_dump($query -> num_rows()) ;

// DB connection
class DB {

    public $connection;

    public function __construct($host , $dbname , $username , $password) {
        $this->connection = new \PDO('mysql:host=' . $host . ';dbname=' . $dbname , $username , $password);
    }

}

// Wrapper
class DBQuery {

    private $num_rows = 0;
    private $results  = array();

    public function __construct($query , $params = null , $class_name = null , DB $db = null) {

        if ( is_null($db) ) {
            $db = $GLOBALS['default_db'];
        }

        $statement = $db->connection->prepare($query);
        $statement->execute($params);

        $errors = $statement->errorInfo();
        if ( $errors[2] ) {
            throw new \Exception($errors[2]);
        }

        $fetch_style = ($class_name ? \PDO::FETCH_CLASS : \PDO::FETCH_OBJ);
        $this->results = $class_name ? $statement->fetchAll($fetch_style , $class_name) : $statement->fetchAll($fetch_style);
        $this->num_rows += $statement->rowCount();

        while ( $statement->nextrowset() ) {
            $this->results = array_merge($this->results,$class_name ? $statement->fetchAll($fetch_style , $class_name) : $statement->fetchAll($fetch_style));
            $this->num_rows += $statement->rowCount();
        }
    }

    public function num_rows() {
        return $this->num_rows;
    }

    public function results() {
        return $this->results;
    }

}

Answer 4

Since a key requirement seems to be that you can implement this with minimal impact on your current codebase, it would have been helpful if you had told us what interface you currently use for running your queries.

While you could use PDO:

• that means an awful lot of work if you are not already using PDO
• PDO exceptions are horrible

Assuming you are using procedural mysqli (and have a good reason not to use mysqli_prepare()) its not that hard to write something (not tested!):

function wrapAndExecute()
{
   $args=func_get_args();
   $db=array_shift($args);
   $stmt=array_shift($args);
   $stmt_parts=explode('?', $stmt);
   if (count($args)+1!=count($stmt_parts)) {
        trigger_error("Argument count does not match placeholder count");
        return false;
   }
   $real_statement=array_shift($stmt_parts);
   foreach ($args as $k=>$val) {
      if (isnull($val)) {
         $val='NULL';
      } else if (!is_numeric($val)) {
         $val="'" . mysqli_real_escape_string($db, $val) . "'";
      }
      $real_statement.=$val . array_shift($stmt_parts);
   }
   return mysqli_query($db, $real_statement);
}

Note that this does not handle IS [NOT] NULL nicely nor a literal '?' in the statement nor booleans (but these are trivial to fix).