&Follow SJoin OnSugar
Simple musings of a Drupal developer.

update_sql is not my friend

Email |
|
By krs · January 7, 2009 · 0 Comments ·

Oh update_sql(), second most unloved of the drupal database functions (db_rewrite_sql() is worse)! If you've never used it, it is basically a wrapper for db_query() that you can use in hook_update_N() hooks in a module .install file. You may never need to write one of these hooks unless you maintain a module; or like me you prefer to make database changes via code when I'm pushing some new features out to dev, stage, and live servers.

The Drupal 6 and 7 versions of the documentation for this function do mention that "%-substitution parameters are not supported." Definitely also true for the Drupal 5 version I'm still working with. So you can't do variable substitution like you do with db_query() and most of the time thats not a big deal.

There is one very important time though.

When using db_query(), you call it with a syntax like db_query($sql, $variable1, $variable2); Now, you know about database prefixes, and always sing curly braces around your table names in FROMs and JOINs, and that lets Drupal work on multisite installs very well, or talk to more than one DB at a time. So the $sql part of that function call always gets passed through a strtr(), or in English, PHP's string translate function that converts every instace of '{' to the right prefix (generally just deletes it). You have no choice about this, and no way to prevent it from getting every single { and }!

So far so good, because you only use curly braces around the table names, and that works out just fine, right?

Now enter update_sql(). This function doesn't support variable substitution, so your function calls look are just update_sql($sql).

Like update_sql("UPDATE {node} SET title = 'something else' WHERE nid = 10"); - that is, everything has to be passed as part of a single SQL statement (the $sql part above). The SQL statement that always gets run through strtr() looking for '{' and '}'. Which, if you are trying to update a serialized array, or a block's visibility code with PHP, or just want to use a { in a node title, it will be deleted. Every time, without exception.

So if you're using update_sql() and any part of the data contains a '{' or '}' that you need to keep intact - rewrite it as a db_query() with variable substitution. The strtr() will operate on the $sql part, and the $values get substituted in later and stay intact.

Filed in: drupal
Tagged with: escaping, curly braces, strtr(), drupal5, SQL
posted by Anonymous
6/23/09

Thanks for this post! Came across the same problem in D6, and your article confirmed the issue (and solution). Is there any way to get documentation of this issue onto api.drupal.org?


posted by Anonymous
10/16/09

Please check the drupal documentation. Set the variables according to the specs.
create a SQL with title='%s' for string value and %d for decimal value.
Then in the db_query, give the variables in order of useage of your sql.
Then it will be your friend.


posted by
10/26/09

@Anonymous
Um, did you read my post? That's exactly what I wrote :) The whole point was that you have to use db_query() instead of update_sql() in these cases.


posted by Anonymous
4/28/11

db_query is not the way to go in an module.install file. The whole point of update_sql is to pass back any errors during update so you know if something went wrong. The better solution is to make your own function that perfroms the same function but is serialize() safe. If you can the best thing to do is report the problem to the project issue queue and submit a patch that fixes the problem. http://drupal.org/node/667714


If you are already an OnSugar member, or would like to receive email alerts as new comments are made, please login or register for OnSugar.
The content of this field is kept private and will not be shown publicly.

Next Camp!

 

About Me

subscribe

Drupal Kiva Team

Archive

Archives

January 2013
SMTWTFS
 12345
6789101112
13141516171819
20212223242526
2728293031 
March 2011
SMTWTFS
 12345
6789101112
13141516171819
20212223242526
2728293031 
July 2010
SMTWTFS
 123
45678910
11121314151617
18192021222324
25262728293031
November 2009
SMTWTFS
1234567
891011121314
15161718192021
22232425262728
2930 
August 2009
SMTWTFS
 1
2345678
9101112131415
16171819202122
23242526272829
3031 
July 2009
SMTWTFS
 1234
567891011
12131415161718
19202122232425
262728293031