query in calc element with special character

[theboss]

I have this PHP code in calculation eval element:

$db11 = JFactory::getDbo();
$query11 = "SELECT dist FROM ztl_serispest WHERE comu1 = '{zdtl_mp___it11i}' AND comu2 = '{zdtl_mp___it11f}';";
$db11->setQuery($query11);
$views11 = $db11->loadResult();
return $views11;

It works ok but return NULL when comu1 or comu2 contains this character " ' " like " PATE' ".

thanks

 

[troester]

You should always quote input strings
$query11 = "SELECT dist FROM ztl_serispest WHERE comu1 =".$db->quote( '{zdtl_mp___it11i}')." AND comu2 = ".$db->quote('{zdtl_mp___it11f}');

 

[theboss]

I tried this code, but sorry, I think (I'm not php expert) your syntax has errors because retunrs nothing when field is with or without " ' ".

 

[theboss]

I think solution is near something like this:

$db11 = JFactory::getDbo();
$comu1=mysql_real_escape_string(comu1);
$comu2=mysql_real_escape_string(comu2);

$query11 = "SELECT dist FROM ztl_serispest WHERE $comu1 = '{zdtl_mp___it11i}' AND $comu2 = '{zdtl_mp___it11f}';";
$db11->setQuery($query11);
$views11 = $db11->loadResult();
return $views11;

that works for value without " ' " in database, but my PHP knowledge is not enough to do it.
I have in database value link "PA'TE" and "PATE'", my test according with:

http://www.php.net/manual/it/function.mysql-real-escape-string.php

 

[troester]

You should quote anyway because of security reasons, see http://www.fabrikar.com/forums/index.php?threads/is-this-possible-with-fabrik.38657/#post-194486
http://xkcd.com/327/

And yes, in your case it must be $db11->quote (I didn't realize that it is $db11 = JFactory::getDbo(); )
Try

$db11 = JFactory::getDbo();
$comu1= $data['zdtl_mp___it11i'];
$comu2= $data['zdtl_mp___it11f'];
 
$query11 = "SELECT dist FROM ztl_serispest WHERE comu1 =". $db11->quote($comu1)." AND comu2 =". $db11->quote($comu2);
$db11->setQuery($query11);
$views11 = $db11->loadResult();
return $views11;

 

[theboss]

thanks troester, It works.

I great help for a PHP "student" like me!

 

[cheesegrits]

Just so you know ... the reason you always need to run any kind of user input though quote() is to prevent SQL injection attacks. In other words, it prevents people from submitting your form with the it11i input set to "(DROP TABLE zdtl_mp_users)" or some such.

It's a good habit to get in to for anyway, regardless of where the data is coming from, but when inserting user input form forms in to a query ... it's absolutely essential.

-- hugh