Re: [PATCH] dlr_mysql.c - Table name fix
Vincent CHAVANIS <[email protected]>
| Newsgroups | gmane.comp.mobile.kannel.devel |
|---|---|
| Message-ID | <[email protected]> |
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.2 KB)
--- dlr_mysql.c 2009-09-04 09:59:31.000000000 +0200
+++ dlr_mysql.c 2009-10-12 12:34:47.612500682 +0200
@@ -103,7 +102,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,
@@ -144,7 +143,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,
@@ -202,7 +201,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);
@@ -234,7 +233,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);
@@ -267,7 +266,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
@@ -301,7 +300,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
@@ -335,6 +334,8 @@
long pool_size;
DBConf *db_conf = NULL;
+#define QUOTES_TFNAME(a) octstr_replace(a, octstr_imm("`"), octstr_imm("``"))
+
/*
* check for all mandatory directives that specify the field names
* of the used MySQL table
@@ -348,6 +349,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