[TYPO3-english] Typoscript SQL query into Formhandler

[Jigal van Hemert]

Hi,

On 21/08/2015 23:04, Christophe Duhamel wrote:
> Ok...
> I found 2 errors in my code :
>
> 1st : I forgot "=" after "JOIN"
> 2nd : It seem it's not possible to have two "join" clause into a
> typoscript "CONTENT" object.

It's possible, but not like you did it. In your case the second join 
property simply overwrote the first one.

Let's go through your TS (I prefixed my comments with #-#):

marker {

#-# I assume this is something from formhandler

   fichier1 = CONTENT
   fichier1 {
     table = sys_file
     select {
       pidInList = 13

         #-# this would mean sys_file.pid, while in your query it
         #-# is sys_file_reference.pid

       #recursive = 10
       #orderBy = crdate ASC
       selectFields = sys_file.uid, sys_file.identifier
       JOIN sys_file_reference ON sys_file.uid = 
sys_file_reference.uid_local

         #-# as you observed it should be: join = ....

       JOIN tx_news_domain_model_news ON sys_file_reference.uid_foreign 
= tx_news_domain_model_news.uid

         #-# you can add this to the previous one; it's just glued
         #-# together in the end

       # possible conditions
       where tx_news_domain_model_news.uid = 
sys_file_reference.uid_foreign AND sys_file_reference.tablenames = 
tx_news_domain_model_news

         #-# here als missing a '=': where = tx_news_.....

       andWhere {
         cObject = COA

           #-# this whole construction would be better done with markers
           #-# as they would take care of escaping any data and you could
           #-# simply use the where property above [1]

         cObject {
           1 = TEXT
           1.data = GP:FormEditnews|title
           1.sanitize = 1

             #-# sanitize is not a TypoScript property provided by the
             #-# core

           1.wrap = tx_news_domain_model_news.title="|"
           #	1.wrap = title="|"
           #	2 = TEXT
           #	2.data = debug:data
           #	2.wrap = debug : |
         }
       }
     }

     renderObj = COA
     renderObj {
       #value
       10.wrap = |
       10 = TEXT
       10.field = identifier
     }
   }
}

The where property would then be:

where = tx_news_domain_model_news.uid = sys_file_reference.uid_foreign 
AND sys_file_reference.tablenames = tx_news_domain_model_news AND 
tx_news_domain_model_news.title="###TITLE###"
markers {
   TITLE.data = GP:FormEditnews|title
}

Now the submitted title is automatically escaped for use in the query 
without giving the possibility to cause SQL injection issues.

[1] 
https://docs.typo3.org/typo3cms/TyposcriptReference/Functions/Select/Index.html 
(bottom of the page)

-- 
Jigal van Hemert
TYPO3 CMS Active Contributor

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