[TYPO3-english] how to: properly escape strings in 4.5.30?
Jigal van Hemert
jigal.van.hemert at typo3.org
Sat Nov 23 08:16:25 CET 2013
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
* escapeStrForLike to escape wildcards in strings used in LIKE
- 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
- $arrayWithFieldsAndValues = array(
'username' => $username,
'lastlogin' => 'NOW()',
'password' => '\'; DROP TABLE fe_users; --',
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
More information about the TYPO3-english