[TYPO3-english] how to: properly escape strings in 4.5.30?

[Jigal van Hemert]

Hi,

On 22-11-2013 21:51, Calgacus map Brude wrote:
> or am I worried over nothing?  is typo3/extbase suseptible to sql
> injection attacks when using the default update and add methods?  If it
> uses string concat to piece together sql then it may be but if it uses
> prepared statement it isn't.  I come from a place that still used alot
> of string concat sql so this worry is just second nature to me.

If you use the t3lib_db functions (using $GLOBALS['TYPO3_DB']-> ) you 
sometimes need to worry:
- if you insert values in query parts (such as where clauses) you need 
to take care of preventing SQL injections. There are a few helper 
functions for you:
  * intval() for integers
  * fullQuoteStr(), fullQuoteArray() and quoteStr() to escape strings. 
You need to supply the table name to those functions to be DBAL 
compatible: if DBAL is used to access DBMS other than MySQL the table 
name is used to determine which DBMS is meant and what escape function 
to call.
  * escapeStrForLike to escape wildcards in strings used in LIKE 
constructions

- for INSERT queries you can supply an array with fieldname-value pairs 
which are all escaped, except the ones in an extra parameter (this way 
you can use SQL functions as values)

- if you use prepared statements the values are escaped for you

- if you use extbase queries in many cases the values are already 
escaped (e.g. the findBy<fieldname> methods escape the value for you); 
if you use raw queries you must take care of escaping yourself

Some examples (fields are imaginary):

- $record = t3lib_BEfunc::getRecordsByField('fe_users', 'username', 
$username, 'lastlogin > ' . intval($lastlogin))
Field value is quoted, but in the where clause I have to take care of 
protection

- $arrayWithFieldsAndValues = array(
   'username' => $username,
   'lastlogin' => 'NOW()',
   'password' => '\'; DROP TABLE fe_users; --',
);
$GLOBALS['TYPO3_DB']->exec_INSERTquery('fe_users', 
$arrayWithFieldsAndValues, array('lastlogin'))
The values in the array are escaped and quoted, but the 'lastlogin' 
field is not quoted because we want it to contain an SQL function.

- $statement = $GLOBALS['TYPO3_DB]->prepare_SELECTquery(
'*', 'fe_users', 'username=:name');
$statement->execute(array(':name' => $name));
The values here are escaped and quoted if necessary.

- $record = $GLOBALS['TYPO3_DB']->exec_SELECTgetSingleRow(
'*', 'fe_users', 'username=' . $GLOBALS['TYPO3_DB']->fullQuoteStr($name, 
'fe_users') . $this->cObj->enableFields('fe_users'));
In the where clause you need to take care of escaping yourself. The call 
to enableFields() adds conditions for fields like hidden/deleted (if 
they are present in the table and with whatever fields were defined in 
the TCA) plus conditions for start and end fields (if applicable) and 
user group fields (if applicable).

-- 
Jigal van Hemert
TYPO3 CMS Active Contributor

TYPO3 .... inspiring people to share!
Get involved: typo3.org