Quoting Strings in SQLite with PHP

Unlike MySQL, SQLite follows the quoting standards in SQL strictly and does not understand the backslash \ as an escape character. SQLite only understands escaping a single quote with another single quote.

For example, if you receive the input data 'cheeky ' string' and use the PHP function addslahes() to escape literal characters in the string then you will get 'cheeky \' string' which according to SQLite is not escaped properly. You need to escape the string so that it looks like 'cheeky '' string'.

If you have magic_quotes turned on then you are in even more trouble. This PHP setting escapes all HTTP variables received by PHP with an equivalent of addslshes(). So the correct way to escape strings in SQLite would be:

function sqlite_quote_string($str) {
 if (get_magic_quotes_gpc()) {
  $str = stripslashes($str);
 }
 return sqlite_escape_string($str);
}

This will remove the escape characters added by the magic_quotes setting, and escape strings with SQLites sqlite_escape_string() function which correctly escapes the string with '.

Creating a custom SQLite Function in PHP

SQLite is available in PHP5 either by compiling PHP5 with SQLite support or enabling the SQLite extension dynamically from the PHP configuration (PHP.ini). A distinct feature of SQLite is that it is an embedded database, and thus offers some features a Server/Hosted database such as the popular MySQL database doesn't.

Creating Custom Functions in SQLite

One of the really cool features of SQLite in PHP is that you can create custom PHP functions, that will be called by SQLite in your queries. Thus you can extend the SQLite functions using PHP.

A custom Regexp function for SQLite in PHP

// create a regex match function for sqlite
sqlite_create_function($db, 'REGEX_MATCH', 'sqlite_regex_match', 2);
function sqlite_regex_match($str, $regex) {
 if (preg_match($regex, $str, $matches)) {
  return $matches[0];
 }
 return false;
}

The above PHP code will create a custom function called REGEX_MATCH for the SQLite connection referenced by $db. The REGEX_MATCH SQLite function is implemented by the sqlite_regex_match user function we define in PHP.

Here is an example query that makes use of the custom function we created. Notice that in the SQLite query, we call our custom function REGEX_MATCH:

$query = 'SELECT REGEX_MATCH(link, \'|http://[^/]+/|i\') AS domain, link, COUNT(link) AS total'
 .' FROM links WHERE domain != 0'
 .' GROUP BY domain'
 .' LIMIT 10';
$result = sqlite_query($db, $query);

This will make SQLite call the PHP function sqlite_regex_match for each database table row that is goes over when performing the select query, sending it the link field value as the first parameter, and the regular expression string as the second parameter. PHP will then process the function and return its results to SQLite, which continues to the next table row.

Custom Functions in SQLite compared to MySQL

In comparison with MySQL, you cannot create a custom function in PHP that mysql will use. MySQL allows creation of custom functions, but they have to be written in MySQL. Thus you cannot extend MySQL's query functionality with PHP.

I believe the reason for this is simply because having a callback function called on the client, by the database, over a Client-Server model for each row that has to be processed would be just inefficient. Imaging processing 100,000 rows in a MySQL database and having MySQL make a callback to PHP over a TCP connection, the overhead of sending the data back and forth for the callback would be way too much.
With an embedded database like SQLite, this isn't the case since making the actual communication between the language and the embedded database does not pose such a high overhead.