[TYPO3-dev] CONTENT object and SQL injection prevention

[Jigal van Hemert]

As an aftermath of Bugday I tried to make a solution for the problem 
that there is no good way to prevent SQL injection problems in queries 
in the TypoScript CONTENT object.
C&C are very much welcome!

I'd like a few opinions before submitting this to the core list.

The patch is available at [1].

The problem
===========
Some of the properties of 'select' support stdWrap and allow thus the 
insertion of all kinds of outside data. There is no option to prevent 
SQL injection problems by properly quoting and escaping these values 
(except for integer values which could be handled with prioricalc int).

The solution
============
The PHP PDO implementation features the use of named parameter markers 
to insert data in queries [2]. The values which are inserted are 
automatically escaped and quoted if necessary, thus preventing SQL 
injection problems with external data.
This patch allows the use of named parameter markers in most of the 
'select' properties.

Example:

10 = CONTENT
10 {
  table = tt_news
  select {
   selectFields = title,uid
   pidInList = 4
   where = title > :whatever
     # :whatever is a named marker, see below for the value definition
   markers {
    whatever.data = GP:first
   }
  }
}

In the new 'markers' property you can setup the named parameter markers 
and their values. Each marker has full stdWrap support, so the data can 
be built from any source.
If the value is an integer or a float (also an integer or float in a 
string) it is inserted as a numerical value, otherwise it's fed through 
t3lib_DB::fullQuoteStr().

Extra features
===============
- All properties (excepting 'andWhere') support these markers, so it 
becomes possible to make paginated output in TS.
- I'd like to nominate andWhere to be marked as deprecated with this 
feature, because the support of stdWrap allows SQL injection problems to 
occur; with named markers the functionality of andWhere is moved to a 
safer place.

Questions
=========
I'm not sure if there is a good way in the core to handle unknown 
markers where some have only a value ['markername'], others have only 
properties ['markername.'] and others have both.
The solution in the patch is to walk through the array and add elements 
with an empty value if there are only properties.


[1] http://www.xs4all.nl/~dcbjht/typo3/namedmarker_trunk.diff
[2] http://www.php.net/manual/en/pdo.prepare.php
-- 
Jigal van Hemert.