Prepared Statements in a Database class.

Question

The Problem

So I'm writing my web based application and it dawns on me "Durr, your stuff is wide open to SQL injection and whatnot! Rewrite db class!"

I'm currently re-writing my $db class and I am having a significant amount of trouble understanding how I'm supposed to implement prepared statements.

Previously...

I used to use something like this:

$db->runQuery("SELECT * FROM someTable WHERE someField = '$var1'");
while ($result = mysql_fetch_array($db->result){
    // ... ugh, tedious
}

Invariably, when performing select statements, I'm grabbing an array, and looping through the results.

I understand that...

1. I should be burnt at the stake for using non-prepared statements in MySQL.
2. I have to let mysql know what type of parameter each variable is. (Or do I)?

I'd like to...

Be able to pass my query, and values to my new function (let's use select as an example) which would then return a result for me to work with (as an assoc. array of values);

$query  = "SELECT * FROM someTable WHERE someField = ? AND anotherField = ?";
$params = array($var1, $var2);
$result = $db->doSelect($query, $params);
// Then do all sorts of neat stuff with $result - huzzah!

I'm having trouble with...

Understanding how I would bring all the information together.

1. How do I present an array of values and have that mushed together with my prepared statement?
2. With said mushed statement, how do I run it (execute()?) and have it return an array?

I'm sorry if my question is somewhat roundabout, however I'm frazzled from trying to understand it. If more information is required, please let me know and I'll add it.

Answer 1

Here is what I've written for a Prepare/Execute function set. These are of course part of a larger DB object.

/**
*   Prepares a query to be run, storing the data in $this->preparedTokens
*   Use the following characters to indicate how the data is to be put into SQL statement
*   ? -> escaped and quoted (with single quotes) before inserting
*   ^ -> inserted as is
*   & -> implodes the array escpaping each value
*   @ -> implodes the array (no escaping)
*
*   @param      string      $sql        The SQL statement to prepare
*
*   @return     int         The key of prepare sql query to be passed to $this->Execute()
*/
public function Prepare($sql) {
    $tokens = preg_split('/((?<!\\\)[@&?^])/', $sql, -1, PREG_SPLIT_DELIM_CAPTURE);

    // loop through removing any escaped values
    foreach ($tokens as $key => $val) {
        switch ($val) {
            case '?' :
            case '&' :
            case '@' :
                break;
            default :
                $tokens[$key] = preg_replace('/\\\([@&?^])/', "\\1", $val);
                break;
        } // switch
    } // foreach

    $this->preparedTokens[] = $tokens;
    end($this->preparedTokens);
    return key($this->preparedTokens);
} // function Prepare

/**
*   Creates the SQL placing the data in the appropriate places and then runs the sql
*
*   @param      int         $preparedKey        The key of the prepared sql
*   @param      array       $data               The array of data to put into the query (the count of this array must match that of the prepared query)
*
*   @return     object      false if the $preparedKey does not exist in $this->preparedTokens
*                           false if count of needed values in sql statement does not equal the number of keys in the data array
*                           otherwise, the result of $this->Query()
*/
public function Execute($preparedKey, $data) {
    if (isset($this->preparedTokens[$preparedKey])) {
        $tokens = $this->preparedTokens[$preparedKey];
        $query = '';
        $dataKey = 0;
        $count = 0;

        // count the number of tokens we have
        $validTokens = array('?', '^', '&', '@');
        foreach ($tokens as $val) {
            if (in_array($val, $validTokens)) {
                ++$count;
            } // if
        } // foreach

        // check to ensure we have the same number of tokens as data keys
        if ($count != count($data)) {
            trigger_error('Query Error: The number of values received in execute does not equal the number of values needed for the query', E_USER_ERROR);
            return false;
        } // if

        // loop through the tokens creating the sql statement
        foreach ($tokens as $val) {
            switch ($val) {
                case '?' :
                    $query .= "'" . $this->EscapeString($data[$dataKey++]) . "'";
                    break;
                case '^' :
                    $query .= $data[$dataKey++];
                    break;
                case '&' :
                    $query .= $this->ImplodeEscape($data[$dataKey++]);
                    break;
                case '@' :
                    $query .= implode(',', $data[$dataKey++]);
                    break;
                default :
                    $query .= $val;
                    break;
            } // switch
        } // foreach

        return $this->Query($query);

    } else {
        return false;
    } // if
} // function Execute

/**
*   Runs $this->Prepare() then $this->Execute() for the sql and the data
*   Use the following characters to indicate how the data is to be put into SQL statement
*   ? -> escaped and quoted (with single quotes) before inserting
*   ^ -> inserted as is
*   & -> implodes the array escpaping each value
*   @ -> implodes the array (no escaping)
*
*   @param      string      $sql        The SQL statement to prepare
*   @param      array       $data       The array of data to put into the query (the count of this array must match that of the prepared query)
*
*   @return     object      returns value from $this->Query() if Execute was successful
*                           otherwise it'll be false
*/
public function PrepareExecute($sql, $data) {
    return $this->Execute($this->Prepare($sql), $data);
} // function PrepareExecute

$this->Query() executes the MySQL statement and then returns different values depending on what the statement is (based on the first 6 characters of the statement, trimmed):

• false if failed (use $this->GetError() to get error message)
• if successful INSERT, then the insert id
• if successful DELETE or UPDATE or REPLACE, then the number of affected rows
• if successful SELECT or any other query type, then the Query object

I'm not sure if this is what you are looking for, but it might help.

Forgot to mention this, but most of the ideas came from the Pear::DB class: http://pear.php.net/package/DB

Answer 2

See if you can make use of following. Let me know if you need a more detailed implementation

call_user_func_array(array($stmt,"bind_result"), $params);