2

Working on Drupal 7.54

I am using a custom PHP/SQL Rules to synchronize Drupal field values with an external database.

I am trying to export a text field value but sometimes I have single quotes in these values.

Consequently, when I try to use a field token in my SQL query, it fails because of this single quote that is not escaped.

I have read a few things about replacing strings and escaping quotes but I did not manage to get my query working (replace? str_replace? addslashes? htmlspecialchars? decode_entities?...).

Here is an example :

Let's say I have a Drupal field called "Text-Drupal" in a content type A.

Each time a content of type A is updated, I need to export the Text-Drupal value in an external database in the column text_database

The little code :

db_set_active('database_local');
db_query("UPDATE table 
SET text_database=NULLIF('[node:field-text-drupal]','')
WHERE title=[node:title]");
db_set_active();

This code works fine with any text without special characters but each time I have a single quote, it fails (and I guess it would be the same with double quotes).

I am quite sure this is something really simple for experienced users so I hope someone could point me to the right direction. Thanks.

2

Disclaimer: you shouldn't be using the PHP filter module, ever, it's very bad practice and can open your site up to security holes. Adding a custom rules action is very easy and should always be the preferred implementation method for something like this.

As a technical exercise, though, something like this might work:

$text = <<<EOF
[node:field-text-drupal]
EOF;

$title = <<<EOF
[node:title]
EOF;

$args = [':text' => $text, ':title' => $title];
$sql = "UPDATE table SET text_database = NULLIF(:text, '') WHERE title = :title";
db_query($sql, $args);
  • Thank you for your answer. Now I need practice to create a custom Rules actions but I am sure it is worth trying. – Toki Jun 16 '17 at 16:31
  • I have tried the EOF part to get my "clean" token and I am afraid it is not working :( Did I miss something here (I always got syntax error if I try to echo the variable) – Toki Jun 16 '17 at 17:49
  • Maybe it's the NULLIF causing the problem, or maybe PHP filter doesn't work with/ignores tokens inside heredocs. I don't have it installed so I can't check, sorry – Clive Jun 18 '17 at 18:55
  • For the moment, the only way I found to get my query working is to use this token format '[node:field_text_drupal]'. My "noob" code is working but of course single quotes appear as &#039 in the external database, now... – Toki Jun 19 '17 at 9:12
0

Here is my own solution to send long text tokens to an external database without single quote problems :

     $token="[node:field-text-drupal]";
     $token_clean=addslashes($token);
     db_set_active('database_local');
     db_query("UPDATE table 
       SET text_database=NULLIF('$token_clean','')
       WHERE title=[node:title]");
     db_set_active();

With addslashes, the token value could contain any quotes without interfering with the query and the database displays the right value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.