stmllr.net

Update

Please have a look at the comments on this article to read about updates and changes done in the TYPO3 core to tackle this problem.

I recommend to use markers for inserting parameters into queries as explained in https://docs.typo3.org/typo3cms/TyposcriptReference/Functions/Select/Index.html.

Introduction

As I have already shown in an article have a year ago, TypoScript is not save against SQL injections. I introduced a case, where a numerical GET parameter (a uid as integer) is used in the WHERE clause of a TypoScript CONTENT cObject. But the parameter could be misused to inject malicious SQL into the WHERE clause. The solution was to parse the GET parameter through the stdWrap function intval. It ensured that only integer values were passed to the WHERE clause.

The posting you are now about to read is meant as a follow-up to that article. It demonstrates a more advanced case: using a string of a GET parameter inside an SQL query of CONTENT.

Example code

Imagine you offer some search function to your website visitors. The search term is send as a GET parameter and TypoScript compares the term with the header of the content elements of a page. All content element of a page which is matching this comparison are shown. The page content is:

We use the TypoScript cObject CONTENT to output all matching content elements of a page. The search term is inserted into the WHERE part of the SQL query:

page.10 = CONTENT
page.10 {
  table = tt_content
  select {
    pidInList = 35
    andWhere.cObject = TEXT
    andWhere.cObject {
      data = GPvar:tx_myext|header
      wrap = header=”|”    
    }
  }
} 

Result

If the URL contains the GET parameter tx_myext[header] it uses the given value in the SQL WHERE clause. For example, try this URL to get all content elements which use the term My products as header:

http ://www.example.com/index.php?id=123&tx_myext[header]=My products

That will result in a SQL clause like this:

SELECT * FROM tt_content ... WHERE ... AND header=”My products” ...

Have a look at the output of this TypoScript object. In the frontend, you'll see this:

SQL injection vulnerability

With the TypoScript above, your website is vulnerable to SQL injections. Imagine a visitor uses the following malicious URL for SQL injection:

http ://www.example.com/index.php?id=123&tx_myext[header]=abc" OR 1=1 OR 1="1

This will result in a SQL clause:

SELECT * FROM tt_content ... WHERE ... AND header=”abc” OR 1=1 OR 1=”1” ...

In the frontend, you'll see something like this:

The website shows all content elements, no matter if they are deleted or hidden!

Solution: quoting and escaping the string

To prevent bad guys from injecting SQL with the search term, you need to quote the string and escape possible control characters. Unfortunately, there's no function in stdWrap to do that.

I have recently released the "quote functions for stdWrap" extension (sm_stdwrapquote) to TER. This extension adds two functions to TypoScript stdWrap:

• quoteStr to escape special values
• fullQuoteStr to escape special values and add quotes

It implements the functionality of t3lib_DB->quoteStr() and t3lib_DB->fullQuoteStr() to stdWrap. This helps you sanitize and safely use GET/POST strings in TypoScript CONTENT objects without too much hassle.

Example code with protection against SQL injection

The extension provides a simple method to protect your TypoScript from SQL injection. You simply add fullQuoteStr to the andWhere part of your CONTENT object. It can be used like a regular function of stdWrap. When using fullQuoteStr, you also don't need quotes in the wrap:

page.10 = CONTENT
page.10 {
  table = tt_content
  select {
    pidInList = 35
    andWhere.cObject = TEXT
    andWhere.cObject {
      data = GPvar:tx_myext|header
      fullQuoteStr = tt_content
      wrap = header=|
    }
  }
} 

The result is that SQL injection will not work anymore. When using the malicious URL, the frontend won't show any content elements at all. fullQuoteStr escapes all quotes inside the GET parameter and quotes the whole string. Data can be safely used with fullQuoteStr inside an SQL SELECT query of CONTENT.

For some more information, read the manual which is shiped with the extension.

Conclusion

You always have to protect your SQL queries of TypoScript cObject CONTENT against SQL injection. Since stdWrap does not ship sufficient functions to sanitize user parameters, use the fullQuoteStr function of the extension sm_stdwrapquote.

Future implementation in TYPO3 core

There's an RFC in the TYPO3 core team list to implement these functions into the core. Unfortunately this did not happen with version 4.3. But I try to push this into 4.4. If you like it, go to the core list and vote for feature #12094.

Related articles

• Is SQL injection possible in TypoScript objects? (March 5, 2009)