Simple prepared statements

[Kudose]

I built my own simple prepared statements section in this stripped down (but should be functional) class. Do you think it is a simple and effective implementation? Any ideas for improvements?

class MyClass {
    public function __construct(array $init){
      $this->_info['host'] = $init['host'];
      $this->_info['user'] = $init['user'];
      $this->_info['pass'] = $init['pass'];
      $this->_info['db'] = $init['db'];
      $this->getConnection();
    }

public function getConnection(){
  if($this->_link === NULL)
    $this->_link = mysql_connect($this->_info['host'], $this->_info['user'], $this->_info['pass']);
  if(!$this->_link)
    throw new Exception('Database connection failure');
  if(!mysql_select_db($this->_info['db'], $this->_link))
    throw new Exception('Database selection error');
  return $this;
}

/**
 * Function to replicate mysql_real_escape_string
 *
 * @param string $text
 * @return string
 */
protected static function clean($text){
  $replace = array(
                "\x00"  => '\x00',
                "\n"    => '\n',
                "\r"    => '\r',
                '\\'    => '\\\\',
                "'"     => "\'",
                '"'     => '\"',
                "\x1a"  => '\x1a'
              );
  return strtr($text, $replace);
}

protected function prepare($sql, array $data = array()){
  $data = array_map(array('MyClass', 'clean'), $data);
  $numToReplace = substr_count($sql, '?');
  for($i = 0; $i < $numToReplace; $i++){
    $sql = preg_replace("#\?#", $data[$i], $sql, 1);
  }
  return $sql;
}

   public function query($sql, array $data = array()){
      $q = mysql_query($this->prepare($sql, $data), $this->_link);
      if(!$q)
        return mysql_error($this->_link);
      return $q;
    }

public function row($sql, array $data = array()){
  $q = $this->query($sql, $data); // query resource
  return mysql_fetch_assoc($q);
}

public function rows($sql, array $data = array()){
  $q = $this->query($sql, $data); // query resource
  $d = array(); // data holder
  while(($r = mysql_fetch_assoc($q)) !== FALSE)
    $d[] = $r;
  return $d;
}
}

$class = new MyClass($db_init);
$results = $class->rows("SELECT `something FROM `table` WHERE `id` = '?' AND `id2` = '?'", array($id, $id2));
echo '<pre>' . print_r($results, true) . '</pre>';

Your thoughts?

[Kudose]

I guess it could be as simple as this too:

function prepare($sql, array $data = array()){
      $data = array_map('mysql_real_escape_string', $data);
      $numToReplace = substr_count($sql, '?');
      for($i = 0; $i < $numToReplace; $i++)
        $sql = preg_replace("#\?#", $data[$i], $sql, 1);
      return $sql;
    } 

echo prepare("SELECT `something FROM `table` WHERE `id` = '?' AND `id2` = '?'", array($id, $id2));

[laserlight]

That does not look good. One feature of prepared statements is that the data is bound according to the desired type. This means that we can write:

SELECT `something FROM `table` WHERE `id` = ? AND `id2` = ?

then bind strings to the placeholders, and it will work safely. But your implementation requires that the strings be quoted in the SQL statement. Along the same lines, it does not appear to correctly handle non-string input.

[Kudose]

Thanks 🙂

Are leaving the quotes just a standard way of doing things or is there a purpose?

[laserlight]

Kudose wrote:

Are leaving the quotes just a standard way of doing things or is there a purpose?

I am not certain of the reasons behind this, but one possibility is that if the user decides to bind a value by a non-string type, the presence of the quotes would then result in a semantic mismatch. On the other hand, it does not seem likely to me that a user would correctly bind a value by a non-string type when the placeholder was originally meant to stand in for a string.

[Kudose]

Good points.

[Kudose]

How about this:

protected function prepare($sql, array $data = array()){
      if(!count($data)) return $sql;
      $data = array_map('mysql_real_escape_string', $data);
      $numToReplace = substr_count($sql, '?');
      for($i = 0; $i < $numToReplace; $i++){
        if(is_int($data[$i]))
          $sql = preg_replace("#\?#", $data[$i], $sql, 1);
        elseif(is_string($data[$i]))
          $sql = preg_replace("#\?#", "'".$data[$i]."'", $sql, 1);
        else
          throw new Exception('May only prepare int and string data types');
      }
      return $sql;
    }

?

[Kudose]

Updated to check binding count:

protected function prepare($sql, array $data = array()){
      $dataCount = count($data);
      if(!$dataCount) return $sql;
      $data = array_map(array('Database', 'clean'), $data);
      $numToReplace = substr_count($sql, '?');
      if($dataCount != $numToReplace) throw new Exception('Data count does not match bind count');
      for($i = 0; $i < $numToReplace; $i++){
        if(is_int($data[$i]))
          $sql = preg_replace("#\?#", $data[$i], $sql, 1);
        elseif(is_string($data[$i]))
          $sql = preg_replace("#\?#", "'".$data[$i]."'", $sql, 1);
        elseif(isset($data[$i]) && !is_array($data[$i]))
          $sql = preg_replace("#\?#", "'".$data[$i]."'", $sql, 1);
        else
          throw new Exception('May only prepare int and string data types and the following was supplied: '.$data[$i]);
      }
      return $sql;
    }