Secure Queries

[Wolphie]

This is a bit much to ask, but i've looked everywhere (even attempted it myself and failed). I'm trying to secure all MySQL queries being made.

The logic of it is, "all data being inserted/updated IS an injection (or possibly dangerous)". Since i suck with Regular Expressions, i was wondering if anybody could give me an example of how splitting up a query string would work.

 

(The idea is to do much like what drupal does, splits up the query, uses a query helper [much like sprintf] to secure and replace the data. And then return it in the original function)

[rarebit]

What rules are you wanting to apply? Are you just wanting to replace dangerous elements or qualify the statement, almost repairing it?

 

A program which makes rules for compilers is 'flex', here's and interesting set of documents on writing your own compiler (not that this is what your wanting to do, but could enlighten certain areas)

[947740]

I think you are being over paranoid.  Theoretically, going by your logic, you should be afraid of the queries that you perform.

[micmania1]

You can never be too safe.

 

mysql_real_escape_string()

htmlspecialchars()

addslashes()

 

Example:

function escape_data($data) {
          // Make it suitable for insertion to mysql and parse html
          return mysql_real_escape_string(htmlspecialchars($data));
}

 

Using it:

$data = '\'<b>I\'m going to destroy this page with my own html.</b>';


$data = escape_data($data);

[Wolphie]

It's not being over paranoid. It's assuming that each query being made has the potential to be a major security risk, therefore we secure it to prevent such a risk even if it was a harmless query. I don't want the entire thing done for me, just something to help me understand HOW to accomplish it.

 

Drupal snippet:

<?php
/**
* Helper function for db_query().
*/
function _db_query_callback($match, $init = FALSE) {
  static $args = NULL;
  if ($init) {
    $args = $match;
    return;
  }

  switch ($match[1]) {
    case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
      return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe
    case '%s':
      return db_escape_string(array_shift($args));
    case '%%':
      return '%';
    case '%f':
      return (float) array_shift($args);
    case '%b': // binary data
      return db_encode_blob(array_shift($args));
  }
}

function _db_rewrite_sql($query = '', $primary_table = 'n', $primary_field = 'nid', $args = array()) {
  $where = array();
  $join = array();
  $distinct = FALSE;
  foreach (module_implements('db_rewrite_sql') as $module) {
    $result = module_invoke($module, 'db_rewrite_sql', $query, $primary_table, $primary_field, $args);
    if (isset($result) && is_array($result)) {
      if (isset($result['where'])) {
        $where[] = $result['where'];
      }
      if (isset($result['join'])) {
        $join[] = $result['join'];
      }
      if (isset($result['distinct']) && $result['distinct']) {
        $distinct = TRUE;
      }
    }
    elseif (isset($result)) {
      $where[] = $result;
    }
  }

  $where = empty($where) ? '' : '('. implode(') AND (', $where) .')';
  $join = empty($join) ? '' : implode(' ', $join);

  return array($join, $where, $distinct);
}

function db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid',  $args = array()) {
  list($join, $where, $distinct) = _db_rewrite_sql($query, $primary_table, $primary_field, $args);

  if ($distinct) {
    $query = db_distinct_field($primary_table, $primary_field, $query);
  }

  if (!empty($where) || !empty($join)) {
    $pattern = '{
      # Beginning of the string
      ^
      ((?P<anonymous_view>
        # Everything within this set of parentheses is named "anonymous view"
        (?:
          [^()]++                   # anything not parentheses
        |
          \( (?P>anonymous_view) \)          # an open parenthesis, more "anonymous view" and finally a close parenthesis.
        )*
      )[^()]+WHERE)
    }x';
    preg_match($pattern, $query, $matches);
    if (!$where) {
      $where = '1 = 1';
    }
    if ($matches) {
      $n = strlen($matches[1]);
      $second_part = substr($query, $n);
      $first_part = substr($matches[1], 0, $n - 5) ." $join WHERE $where AND ( ";
      // PHP 4 does not support strrpos for strings. We emulate it.
      $haystack_reverse = strrev($second_part);
    }
    else {
      $haystack_reverse = strrev($query);
    }
    // No need to use strrev on the needle, we supply GROUP, ORDER, LIMIT
    // reversed.
    foreach (array('PUORG', 'REDRO', 'TIMIL') as $needle_reverse) {
      $pos = strpos($haystack_reverse, $needle_reverse);
      if ($pos !== FALSE) {
        // All needles are five characters long.
        $pos += 5;
        break;
      }
    }
    if ($matches) {
      if ($pos === FALSE) {
        $query = $first_part . $second_part .')';
      }
      else {
        $query = $first_part . substr($second_part, 0, -$pos) .')'. substr($second_part, -$pos);
      }
    }
    elseif ($pos === FALSE) {
      $query .= " $join WHERE $where";
    }
    else {
      $query = substr($query, 0, -$pos) . " $join WHERE $where " . substr($query, -$pos);
    }
  }

  return $query;
}

function db_query($query) {
  $args = func_get_args();
  array_shift($args);
  $query = db_prefix_tables($query);
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
  }
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  return _db_query($query);
}

function _db_query($query, $debug = 0) {
  global $active_db, $queries, $user;

  if (variable_get('dev_query', 0)) {
    list($usec, $sec) = explode(' ', microtime());
    $timer = (float)$usec + (float)$sec;
    // If devel.module query logging is enabled, prepend a comment with the username and calling function
    // to the SQL string. This is useful when running mysql's SHOW PROCESSLIST to learn what exact
    // code is issueing the slow query.
    $bt = debug_backtrace();
    // t() may not be available yet so we don't wrap 'Anonymous'.
    $name = $user->uid ? $user->name : variable_get('anonymous', 'Anonymous');
    // str_replace() to prevent SQL injection via username or anonymous name.
    $name = str_replace(array('*', '/'), '', $name);
    $query = '/* '. $name .' : '. $bt[2]['function'] .' */ '. $query;
  }

  $result = mysql_query($query, $active_db);

  if (variable_get('dev_query', 0)) {
    $query = $bt[2]['function'] ."\n". $query;
    list($usec, $sec) = explode(' ', microtime());
    $stop = (float)$usec + (float)$sec;
    $diff = $stop - $timer;
    $queries[] = array($query, $diff);
  }

  if ($debug) {
    print '<p>query: '. $query .'<br />error:'. mysql_error($active_db) .'</p>';
  }

  if (!mysql_errno($active_db)) {
    return $result;
  }
  else {
    // Indicate to drupal_error_handler that this is a database error.
    ${DB_ERROR} = TRUE;
    trigger_error(check_plain(mysql_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
    return FALSE;
  }
}
?>