How prepare variables with quote before MySQL statements?

R.IN.G.O Lavoro Over 40

R.IN.G.O Lavoro Over 40

Member
I have a form to manage Schedule.
My problem is that params field contains single quotes (') and double quotes (").

I tried addslashes(), but it puts back-slashes before double quotes (") but not before single quotes ('), so I get a MySQL error.
I also tried str_replace() using a lot of combinations of single/double quotes, but I wasn't able to get the right result.

How can I solve?
 
I have a lot of problems for this specific form, but my question is more general.

I have a form (with his table) to manage parameters of Schedules.
I fill the table using a php_events - onPreLoadData List plugin; in the case of email Schedule, I extract Subject and Message from params field and put them in specifics fields.

So, the form contains elements in which users can put
single quotes (')​
double quotes (")​
HTML text (using the editor)​

I use a PHP end of form submission (onAfterProcess) plugin to update nuvrj_fabrik_cron:
PHP: 
$query = "UPDATE nuvrj_fabrik_cron
          SET
            modified    = '" . $DataOra_Ultima_Modifica . "',
            modified_by = " . $Utente_Ultima_Modifica . ",
            label      = '" . $Nome . "',
            published  = " . $Stato . ",
            lastrun    = '" . $Data_ora_ultima_esecuzione . "',
            plugin      = '" . $Tipo . "',
            frequency  = " . $Periodicita_esecuzione . ",
            unit        = '" . $Periodicita_esecuzione_UM . "',
            params      = '" . $Parametri_new . "'
          WHERE id = " . $id;

I have problems especially with the field params because it contains single quotes ('), double quotes (") and HTML text.
I tried
params = '" . addslashes($Parametri_new) . "'​
params = '" . $db->quote($Parametri_new) . "'​

I tried the query directly in phpMyAdmin, but I had errors due to single quotes (') and also due to HTML.

So, my general question is: how I have to treat variables to put in the query?


---EDIT---
In other words, I need the same statements used in Fabrik/Schedule to insert/edit values in MySQL.
I wasn't able to find them.
 
Fabrik params are JSON encoded.
But you really must know what you are doing if you are hacking Fabrik meta data tables.
 
params = '" . $db->quote($Parametri_new) . "'
Click to expand...

Why the extra single quotes? If you are using $db->quote(), that's going to wrap the value in quotes. So ...

params = " . $db->quote($Parametri_new) . "
Click to expand...

And you should run everything you get from a form through $db->quote() anyway, regardless, of any other issues, to avoid SQL injection attacks.

Things are also a little bit easier if you use the "new" query builder, rather than just stringing queries together by hand. there's loads of documentation on it, but here's a good example page on J!'s site for some general purpose stuff, like $query->update()->set().

https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase

-- hugh
 
Oh, and I'll echo what Troester said. If you are modifying our metadata, make darn sure you know what you are doing, and I'd advise testing on a copy of the table first.

-- hugh
 
Thanks, troester and cheesegrits.

I'm sorry, but I don't know :oops: what it means " hacking Fabrik meta data tables".
I need only
- read Fabrik table nuvrj_fabrik_cron to put values on my table for my form (also extracting "subject" and "message" from params field)
- allow modifications on the form
- write modification in nuvrj_fabrik_cron (after rebuilt the value to put in params, using modified "subject" and "message")

My code (abstract):
php_events - onPreLoadData List plugin
PHP: 
foreach($righe_da___nuvrj_fabrik_cron as $riga_Da)
  {....[
        $params = $riga_Da['params'];
    $param_array = explode(",", $params);[/SIZE]
    if($plugin == 'email')
    {$Oggetto_email = $param_array['6'];  //6 subject
        if($Oggetto_email != '')
          {$Oggetto_email = str_ireplace('"subject":"', '', $Oggetto_email);
            $Oggetto_email = substr($Oggetto_email,0,-1);
          }
 
        (the same for "message")
        ....
    $query=  "INSERT INTO fabrik_parametri_esecuzione_automatica
        ( id,.... Parametri, Oggetto_email, Testo_email)
      VALUES
        (" . $id . ", ' .... addslashes($params) . "', " . "'" . addslashes($Oggetto_email) . "', '" . addslashes($Testo_email) . "')";

PHP end of form submission (onAfterProcess) plugin
PHP: 
if($Tipo == 'email')
 {$param_old_array = explode(",", $Parametri_old);
   $Oggetto_email_old = $param_old_array['6'];  //6 subject
    $Oggetto_email_new = '"subject":"' . $Oggetto_email_new . '"';    // "subject":"Avviso pagamento quota Rinnovo Iscrizione"
 (the same for "message")
  ....
   $Parametri_new = str_replace($Oggetto_email_old, $Oggetto_email_new, $Parametri_old);
    $Parametri_new = str_replace($Testo_email_old, $Testo_email_new, $Parametri_new);
 }
else
  {$Parametri_new = $Parametri_old;}
 
$query = "UPDATE nuvrj_fabrik_cron 
          SET 
              params      = " . $db->quote($Parametri_new) . "
          WHERE id = " . $id;
 
I modified PHP end of form submission (onAfterProcess) plugin as follows:
$query = "UPDATE nuvrj_fabrik_cron ​
SET ​
modified = " . $db->quote($DataOra_Ultima_Modifica) . ", ​
modified_by = " . $Utente_Ultima_Modifica . ", ​
label = " . $db->quote($Nome) . ", ​
published = " . $Stato . ", ​
lastrun = " . $db->quote($Data_ora_ultima_esecuzione) . ", ​
plugin = " . $db->quote($Tipo) . ", ​
frequency = " . $Periodicita_esecuzione . ", ​
unit = " . $db->quote($Periodicita_esecuzione_UM) . ", ​
params = " . $db->quote($Parametri_new) . " ​
WHERE id = " . $db->quote($id);​
​
echo $query;​
exit;​

to get the query.
In phpMyAdmin, I tried:
UPDATE nuvrj_fabrik_cron SET modified = '2015-05-14 17:27:00', modified_by = 42, label = 'Invio email sollecito pagamento Quota (prima) Iscrizione', published = 0, lastrun = '2015-05-07 06:00:00', plugin = 'email', frequency = 3, unit = 'day', ​
​
params = '{"connection":"1","table":"95","log":"0","log_email":"","require_qs":"0","to":"{fabrik_nominativi___E_mail}",\"subject\":\"Avviso per l'iscrizione dell'utente\",\"message\":\"<p>Devi pagare per l'iscrizione</p>\","cronemail-eval":"0","cronemail_condition":"return '{fabrik_nominativi___Stato_Simpatizzante_Socio_raw}' == 2;","cronemail-updatefield":"","cronemail-updatefield-value":"","cronemail-updatefield-eval":"0"}'​
​
​
WHERE id = '1'​
​
I got
Errore

Messaggio di MySQL:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'iscrizione dell'utente\",\"message\":\"<p>Devi pagare per l'iscrizione</p>\","cr' at line 3


Then I modified the query in phpMyAdmin (see red: \ before ' )
UPDATE nuvrj_fabrik_cron SET modified = '2015-05-14 17:27:00', modified_by = 42, label = 'Invio email sollecito pagamento Quota (prima) Iscrizione', published = 0, lastrun = '2015-05-07 06:00:00', plugin = 'email', frequency = 1, unit = 'day',​
​
params = '{"connection":"1","table":"95","log":"0","log_email":"","require_qs":"0","to":"{fabrik_nominativi___E_mail}",\"subject\":\"Avviso per l\'iscrizione dell\'utente\",\"message\":\"<p>Devi pagare per l\'iscrizione</p>\","cronemail-eval":"0","cronemail_condition":"return \'{fabrik_nominativi___Stato_Simpatizzante_Socio_raw}\' == 2;","cronemail-updatefield":"","cronemail-updatefield-value":"","cronemail-updatefield-eval":"0"}'​
​
WHERE id = '1'​
​
It worked without errors.


$db->quote() doesn't work well? (As I wrote in my first post)
But it is strange: it works in PHP end of form submission (onAfterProcess) plugin (see my previous post).

How can I solve?
How can I substitute ' to \' ? I wasn't able.
 
" hacking Fabrik meta data tables"
Click to expand...
You are trying to modify a Fabrik core (meta data) table, which may break your whole Fabrik installation if it's not done correctly (maybe not so dangerous in case of the cron table which is fairly "isolated" but I don't want to imagine what will happen if somebody is touching the joins table...)

As I've said: the params are JSON encoded.
You can't rely on xx[6] containing the subject.
Do something like
$cron_params = json_decode($riga_Da['params']);

Then you have the subject in $cron_params->subject, message in $cron_params->message
and you can set
$cron_params->subject = $new_subject;//maybe you have to escape with addslashes or mysqli_real_escape_string
$cron_params->message = $new_message;
..
$params_encoded = json_encode($cron_params);

Then update https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase#Using_SQL_2

Your example is surely breaking the cron because you are escaping the "keys" subject and message
\"subject\":
And what should happen if there are multiple cron email plugins? (...if($plugin == 'email')....)
 
I didn't solved.

I tried:
PHP: 
.....
$Oggetto_email_new = '{fabrik_parametri_esecuzione_automatica___Oggetto_email}';
$Testo_email_new = '{fabrik_parametri_esecuzione_automatica___Testo_email}';
$Tipo = '{fabrik_parametri_esecuzione_automatica___Tipo}';
$Parametri_old = '{fabrik_parametri_esecuzione_automatica___Parametri}';
 
if($Tipo == 'email')
  {
    $cron_params = json_decode($Parametri_old);
    $cron_params->subject = $Oggetto_email_new;
    $cron_params->message = $Testo_email_new;
    $Parametri_new = json_encode($cron_params);
  }
else
  {$Parametri_new = $Parametri_old;}
 
print_r("Testo_email_new: " . $Testo_email_new . "<br /><br />");
print_r("Parametri_old: " . $Parametri_old . "<br /><br />");
print_r("Parametri_new: " . $Parametri_new . "<br /><br />");

I got:
Testo_email_new: <p>Devi pagare per l'iscrizione!</p> (the element is a textarea with WYSIWYG Editor = YES)

Parametri_old: {"connection":"1","table":"95","log":"0","log_email":"","require_qs":"0","to":"{fabrik_nominativi___E_mail}","subject":"Soggetto dell'avviso","message":"Devi pagare per l'iscrizione","cronemail-eval":"1","cronemail_condition":"return '{fabrik_nominativi___Stato_Simpatizzante_Socio_raw}' == 2;","cronemail-updatefield":"","cronemail-updatefield-value":"","cronemail-updatefield-eval":"0"}

Parametri_new: {"subject":"Soggetto dell'avviso","message":"<p>Devi pagare per l'iscrizione!<\/p>"}


So, Parametri_new contains only modified values.

Can I use a textarea with WYSIWYG Editor = YES for message? Eval is set to YES.
 
It's working on my site.
Add
echo '<pre>';var_dump($cron_params);
after json_decode and before json_encode
 
Ok.

Result:

object(stdClass)#711 (2) {​
["subject"]=>​
string(25) "Soggetto dell'avviso"​
["message"]=>​
string(52) "<p>Devi pagare per l'iscrizione</p>"​
}​
​
Testo_email_new: <p>Devi pagare per l'iscrizione</p>​
​
Parametri_old: {"connection":"1","table":"95","log":"0","log_email":"","require_qs":"0","to":"{fabrik_nominativi___E_mail}","subject":"Soggetto dell'avviso","message":"Devi pagare per l'iscrizione","cronemail-eval":"1","cronemail_condition":"return '{fabrik_nominativi___Stato_Simpatizzante_Socio_raw}' == 2;","cronemail-updatefield":"","cronemail-updatefield-value":"","cronemail-updatefield-eval":"0"}​
​
Parametri_new: {"subject":"Soggetto dell'avviso","message":"<p>Devi pagare per l'iscrizione<\/p>"}​
​

So the problem is the php_events - onPreLoadData List plugin?
PHP: 
.........
$params = $riga_Da['params'];
 
foreach($righe_da___nuvrj_fabrik_cron as $riga_Da)
    {
        ...
      $plugin = $riga_Da['plugin'];        //php emai
 
    if($plugin == 'email')
      {
        $cron_params = json_decode($riga_Da['params']);
        $Oggetto_email = $cron_params->subject;
        $Testo_email = $cron_params->message;
      }
 
    else
      {
        $Oggetto_email = '';
        $Testo_email = '';
      }
 
    $query=  "INSERT INTO fabrik_parametri_esecuzione_automatica
        ( id, DataOra_Ultima_Modifica, Utente_Ultima_Modifica,
          Nome, Stato,
          Data_ora_ultima_esecuzione, Tipo,
          Periodicita_esecuzione, Periodicita_esecuzione_UM,
          Parametri,
          Oggetto_email, Testo_email)
      VALUES
        (" . $id . ", '" . $modified ."', '" . $modified_by . "', " .
        "'" . $label . "', '" . $published . "', " .
        "'" . $lastrun . "', '" . $plugin . "', " .
        $frequency . ", '" . $unit . "', '" . addslashes($params) . "', " .
        "'" . addslashes($Oggetto_email) . "', '" . addslashes($Testo_email) . "')";
 
    $db->setQuery($query);
    mysql_query($query);
  }
 
Yes, I made what you asked:
PHP: 
if($Tipo == 'email')
  {
    $cron_params = json_decode($Parametri_old);
 
    $cron_params->subject = $Oggetto_email_new;
    $cron_params->message = $Testo_email_new;
 
echo '<pre>';var_dump($cron_params);
 
    $Parametri_new = json_encode($cron_params);
  }
else
  {$Parametri_new = $Parametri_old;}
 
No, you should dump twice.
1st time directly after json_decode
Dump addidionally $Parametri_old (not print_r)
So
$cron_params = json_decode($Parametri_old);
echo '<pre>';var_dump($Parametri_old,$cron_params);
 
I am sorry, I didn't understand.

The result is:
string(668) "{"connection":"1","table":"95","log":"0","log_email":"","require_qs":"0","to":"{fabrik_nominativi___E_mail}","subject":"Soggetto dell'avviso","message":"Devi pagare per l'iscrizione","cronemail-eval":"1","cronemail_condition":"return '{fabrik_nominativi___Stato_Simpatizzante_Socio_raw}' == 2;","cronemail-updatefield":"","cronemail-updatefield-value":"","cronemail-updatefield-eval":"0"}"
NULL
object(stdClass)#1037 (2) {
["subject"]=>
string(41) "Soggetto dell'avviso all'utente"
["message"]=>
string(142) "<p>Devi pagare per l'iscrizione, <span style="color: #ff0000;">altrimenti NON TI ACCETTIAMO!</span></p>"
}

So, $cron_params is null.
It depends on php_events - onPreLoadData List plugin?

I don't understand why: I read $params and write it without modifications.
 
As php_events - onPreLoadData List plugin also I tried:

Code: 
    // Insert columns.
    $columns = array(
                      'id', 'DataOra_Ultima_Modifica', 'Utente_Ultima_Modifica', 'Nome', 'Stato',
                      'Data_ora_ultima_esecuzione', 'Tipo', 'Periodicita_esecuzione', 'Periodicita_esecuzione_UM',
                      'Parametri', 'Oggetto_email', 'Testo_email'
                    );
 
    // Insert values.
    $values = array(
                      $id, $db->quote($modified), $modified_by, $db->quote($label), $published,
                      $db->quote($lastrun), $db->quote($plugin), $frequency, $db->quote($unit),
                      $db->quote($params), $db->quote($Oggetto_email), $db->quote($Testo_email)
                    );
 
    // Prepare the insert query.
    $query
        ->insert($db->quoteName('fabrik_parametri_esecuzione_automatica'))
        ->columns($db->quoteName($columns))
        ->values(implode(',', $values));
 
    $db->setQuery($query);
    $result = $db->execute();

but I got
Fatal error: Call to a member function insert() on a non-object in /web/htdocs/www.lavoro-over40.com/home/joomla/plugins/fabrik_list/php_events/php_events.php(215) : eval()'d code on line 126​
​
Line 126 is
->insert($db->quoteName('fabrik_parametri_esecuzione_automatica'))​
 
Here is how to insert
http://fabrikar.com/forums/index.php?wiki/common-php-tasks/#insert

As I've said: if you are trying to modifiy Fabrik core meta data tables you have to know exactly what you are doing.

Why are you loading the params into a Fabrik element?
Not sure what is happening there, the $Parametri_old string your var_dump is showing doesn't have 668 characters, so there seem to be a lot of non-visible characters (e.g. HTML tags...) which are breaking the JSON structure.

Fetch the params directly from the DB in your php (end of form submission) plugin and modify with subject and message from your form.

I don't understand at all what you are trying to do:
inserting what and why into the DB "on preload"?
updating via "foreach..." all email cron plugins and so modifying all params with the same subject/message on save?
 
Ok, I will try to explain again in other words.

I need a form to modify cron plugins on J! front-end, to avoid give access to back-end.
I thought that wasn't a good idea to create a new List (and Form) directly on nuvrj_fabrik_cron; what do you think about? Would it better?

So, I created a table in which put cron plugins parameters (using the php_events - onPreLoadData List plugin) and to permit users to modify parameters using the form; a PHP end of form submission (onAfterProcess) plugin modifies the nuvrj_fabrik_cron table.
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 157 (members: 1, guests: 156)
    Back
    Top