sql_quote

The sql_quote() function is used to secure or filter data content (with apostrophes) in order to avoid SQL injection attacks. This function is very important and must be used whenever content is provided by user data entry. The sql_insertq, sql_updateq, and sql_replace functions automatically apply this filtering for any inserted data (but not for the other parameters like $where which ought to be filtered nonetheless anyway).

It accepts 3 parameters:

  1. $val is the expression to be filtered,
  2. $serveur,
  3. $type optional, is the type of value expected. This would equal int for an integer value.

It is used as shown below:

  1. $charstring = sql_quote("David's car");
  2. $fieldname = sql_quote($fieldname);
  3. sql_select('column', 'table', 'titre=' . sql_quote($titre));
  4. sql_updateq('table', array('column'=>'value'), 'titre=' . sql_quote($titre));

Download

Whenever a numeric identifier is expected, which is often the case for primary keys, the filtering may simply apply the PHP intval() function (the value zero will be returned if the content passed is not numeric):

  1. $id_table = intval(_request('id_table'));
  2. sql_select('column', 'table', 'id_table=' . intval($id));

Download

Example

The url_delete() function deletes URLs from the SQL table that stores the URLs for SPIP editorial objects. It filters the strings using sql_quote() and uses intval() on the identifier:

  1. function url_delete($objet, $id_objet, $url=""){
  2. $where = array(
  3. "id_objet=" . intval($id_objet),
  4. "type=" . sql_quote($objet)
  5. );
  6. if (strlen($url)) {
  7. $where[] = "url=" . sql_quote($url);
  8. }
  9.  
  10. sql_delete("spip_urls", $where);
  11. }

Download

Author Mark Baber Published : Updated : 02/06/10

Translations : English, français