Cache.Storage.MySQL is incompatible with current mysql driver

[i12o]

Cache.Storage.MySQL is not working well with current Sql.Sql module because of binding variable syntax change.

ex. where cachekey='%s' causes SQL syntax error, throws sql query: where cachekey=''foobar'' Binding variables quoted with double single quote.

I've patched that module, and now it's working as far as I've tested.

[i12o]

This is patch I've written

--- /usr/lib/pike8.0/modules/Cache.pmod/Storage.pmod/MySQL.pike 2024-03-10 20:50:21.000000000 +0900
+++ Cache.pmod/Storage.pmod/MySQL.pike  2024-06-04 00:21:24.234804351 +0900
@@ -118,7 +118,7 @@
   if (have_dependants) {
     if (already_deleted && already_deleted[key]) // already deleted. Skip
       return;
-    mixed rv=db->query("select dependants from cache where cachekey='%s'",key);
+    mixed rv=db->query("select dependants from cache where cachekey=%s",key);
     if (rv && sizeof(rv) && rv[0]->dependants) { // there are dependants
       dependants=decode_value(rv[0]->dependants);
     }
@@ -126,7 +126,7 @@
   }

   if (already_deleted) already_deleted[key]=1;
-  db->query("delete from cache where cachekey='%s'",key);
+  db->query("delete from cache where cachekey=%s",key);

   if (dependants) {
     foreach (indices(dependants),string dep) {
@@ -142,12 +142,12 @@
          void|multiset(string) dependants) {
   debug("setting value for key %s (e: %d, v: %f",key,expire_time,
         preciousness?preciousness:1.0);
-  db->query("delete from cache where cachekey='%s'",key);
+  db->query("delete from cache where cachekey=%s",key);
   db->query("insert into cache "
             "(cachekey,atime,ctime,etime,cost,data, dependants) "
-            "values('%s',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,%s,%s,'%s','%s')",
+            "values(%s,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,%s,%s,%s,%s)",
             key,
-            (expire_time?"FROM_UNIXTIME("+expire_time+")":"0"),
+            (db->encode_datetime(expire_time ? expire_time : 0)),
             (preciousness?(string)preciousness:"1"),
             encode_value(value),
             (dependants?encode_value(dependants):"NULL")
@@ -164,12 +164,12 @@
   catch (result=db->query("select unix_timestamp(atime) as atime,"
                           "unix_timestamp(ctime) as ctime,"
                           "unix_timestamp(etime) as etime,cost,data "
-                          "from cache where cachekey='%s'",key));
+                          "from cache where cachekey=%s",key));
   if (!result || !sizeof(result))
     return 0;
   if (!notouch)
     catch(db->query("update cache set atime=CURRENT_TIMESTAMP "
-                    "where cachekey='%s'",key));
+                    "where cachekey=%s",key));
   return Data(result[0]);
 }

[grubba]

Thanks for the report and patch.

Issue copied to the main bugtracker PIKE #10167.

[grubba]

Fixed in Pike 8.0 and master.

Please verify.

[i12o]

I had tested with my code, your change worked fine. Thank you Grubba-san.