Re: [PATCH] dlr_mysql.c - Table name fix

Vincent CHAVANIS <[email protected]> Mon, 12 Oct 2009 13:45:14 +0200

Newsgroups	gmane.comp.mobile.kannel.devel
Message-ID	<[email protected]>

done.

Vincent


Alexander Malysh a écrit :
> Hi again,
> 
> I don't see reason to use macro because macro contains only one line...
> Could you please drop macro and use plain function call?
> 
> Thanks,
> Alex
> 
> Am 12.10.2009 um 12:39 schrieb Vincent CHAVANIS:
> 
>>
>> Damn! you're right ;-)
>>
>> For this issue, i decided to use a macro that
>> replaces names during the init process
>>
>> Comments ?
>>
>> Vincent.
>>
>>
>> Alexander Malysh a écrit :
>>> Hi,
>>> +1 for this patch but patch is not complete. It doesn't handle this 
>>> case:
>>> CREATE TABLE `a``b` (`c"d` INT);
>>> Would you like update your patch? I think as optional callback 
>>> function ?
>>> Thanks,
>>> Alex
>>> Am 09.10.2009 um 16:27 schrieb Vincent CHAVANIS:
>>>>
>>>> this is Mysql specs (cf 
>>>> http://dev.mysql.com/doc/refman/5.0/en/identifiers.html)
>>>> eg. : A reserved word that follows a period in a qualified name must 
>>>> be an identifier, so it need not be quoted.
>>>>
>>>> Then, binds have absolutly nothing to do with this patch
>>>> I'm talking about field/table names specified on the config file.
>>>> But if binds are not escaped, then we need regular quotes is strings.
>>>>
>>>> Actually if you have a table/field names called SELECT or UPDATE or 
>>>> LIMIT or whatever reserved by mysql
>>>> then the SQL parsing error will occur. (And you have plenty of 
>>>> these, please check the link below)
>>>> (http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html)
>>>>
>>>> Vincent.
>>>>

mysql_table_quotes.patch (text/plain, 3.4 KB)

--- dlr_mysql.c 2009-10-12 13:41:52.204661013 +0200
+++ dlr_mysql.c 2009-10-12 13:42:57.044375347 +0200
@@ -82,6 +81,8 @@
  */
 static struct dlr_db_fields *fields = NULL;
 
+static void quotes_tfname (Octstr * str);
+
 static void dlr_mysql_shutdown()
 {
     dbpool_destroy(pool);
@@ -102,7 +103,7 @@
         return;
     }
 
-    sql = octstr_format("INSERT INTO %S (%S, %S, %S, %S, %S, %S, %S, %S, %S) VALUES "
+    sql = octstr_format("INSERT INTO `%S` (`%S`, `%S`, `%S`, `%S`, `%S`, `%S`, `%S`, `%S`, `%S`) VALUES "
                         "(?, ?, ?, ?, ?, ?, ?, ?, 0)",
                         fields->table, fields->field_smsc, fields->field_ts,
                         fields->field_src, fields->field_dst, fields->field_serv,
@@ -143,7 +144,7 @@
     if (pconn == NULL) /* should not happens, but sure is sure */
         return NULL;
 
-    sql = octstr_format("SELECT %S, %S, %S, %S, %S, %S FROM %S WHERE %S=? AND %S=? LIMIT 1",
+    sql = octstr_format("SELECT `%S`, `%S`, `%S`, `%S`, `%S`, `%S` FROM `%S` WHERE `%S`=? AND `%S`=? LIMIT 1",
                         fields->field_mask, fields->field_serv,
                         fields->field_url, fields->field_src,
                         fields->field_dst, fields->field_boxc,
@@ -201,7 +202,7 @@
     if (pconn == NULL)
         return;
 
-    sql = octstr_format("DELETE FROM %S WHERE %S=? AND %S=? LIMIT 1",
+    sql = octstr_format("DELETE FROM `%S` WHERE `%S`=? AND `%S`=? LIMIT 1",
                         fields->table, fields->field_smsc,
                         fields->field_ts);
 
@@ -233,7 +234,7 @@
     if (pconn == NULL)
         return;
 
-    sql = octstr_format("UPDATE %S SET %S=? WHERE %S=? AND %S=? LIMIT 1",
+    sql = octstr_format("UPDATE `%S` SET `%S`=? WHERE `%S`=? AND `%S`=? LIMIT 1",
                         fields->table, fields->field_status,
                         fields->field_smsc, fields->field_ts);
 
@@ -266,7 +267,7 @@
     if (conn == NULL)
         return -1;
 
-    sql = octstr_format("SELECT count(*) FROM %S", fields->table);
+    sql = octstr_format("SELECT count(*) FROM `%S`", fields->table);
 #if defined(DLR_TRACE)
     debug("dlr.mysql", 0, "sql: %s", octstr_get_cstr(sql));
 #endif
@@ -300,7 +301,7 @@
     if (pconn == NULL)
         return;
 
-    sql = octstr_format("DELETE FROM %S", fields->table);
+    sql = octstr_format("DELETE FROM `%S`", fields->table);
 #if defined(DLR_TRACE)
     debug("dlr.mysql", 0, "sql: %s", octstr_get_cstr(sql));
 #endif
@@ -324,6 +325,11 @@
     .dlr_flush = dlr_mysql_flush
 };
 
+static void quotes_tfname(Octstr *str)
+{
+    return octstr_replace(str, octstr_imm("`"), octstr_imm("``"));
+}
+
 struct dlr_storage *dlr_init_mysql(Cfg *cfg)
 {
     CfgGroup *grp;
@@ -347,6 +353,17 @@
     fields = dlr_db_fields_create(grp);
     gw_assert(fields != NULL);
 
+    quotes_tfname(fields->table);
+    quotes_tfname(fields->field_smsc);
+    quotes_tfname(fields->field_ts);            
+    quotes_tfname(fields->field_src);
+    quotes_tfname(fields->field_dst);
+    quotes_tfname(fields->field_serv);
+    quotes_tfname(fields->field_url);
+    quotes_tfname(fields->field_mask);
+    quotes_tfname(fields->field_status);
+    quotes_tfname(fields->field_boxc);
+
     /*
      * now grap the required information from the 'mysql-connection' group
      * with the mysql-id we just obtained