How to escape single quote from a token to use it in SQL query?

Question

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.

Answer 1

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);

Answer 2

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.