[issue] 4.0.3 breaks MySQL access (external tables)

dust · 2005-12-30 20:13:45

I know, this is not a real bug, because it doesn’t affect the working of Textpattern itself. However, it might break some plugins – at least it did break the ones I’m currently working on ;-)

Until version 4.0.2 it was possible to access other tables than the default TXP table using the built-in functions safe_xxx. This is no longer possible because of the changes to txplib_db.php. The functions now escape the table name stored in $table with backticks (“`”). This breaks access to external tables. That’s because MySQL expects references to tables stored in other databases to be referenced either as “mydb.mytable” or “`mydb`.`mytable`”. With the automated escaping you end up with either “`mydb.mytable`” or “``mydb`.`mytable``” which are both illegal. (Sorry for the double quotes but I found no other way to make PunBB display backticks)

The temporary fix is to pass external table references as “mydb`.`mytable”. While this will work, it for sure isn’t elegant and it also is a major source for bugs that are hard to find. So I’d suggest to enhance txplib_db.php in a way so that it converts the table name into the correct format automatically. This could be done using a simple function:

<pre>function tickit($prefix, $string) { // remove all backticks that might exist $string = str_replace(“`”, “”, $string);

// if there’s no dot in the string, it’s a TXP table // so we have to honor any prefix if (!strpos($string, “.”)) { return “`” . $prefix . $string . “`”; } else { // skip the prefix and convert the table // name to a safe string for MySQL $temp = explode(“.”, $string); for ($i=0; $i<count($temp); $i++) { $temp[$i] = “`” . $temp[$i] . “`”; } return implode(“.”, $temp);; } } </pre> So, for example, safe_rows could then look like this: <pre>function safe_rows($things, $table, $where, $debug=’‘) { $q = “select $things from “. tickit(PFX, $table) .” where $where”; $rs = getRows($q,$debug); if ($rs) { return $rs; } return array(); } </pre> And yes, I’m fully aware that the suggested function tickit will also work on illegal names like mydb.mytable.wrongreference. But that’s a user problem, isn’t it? ;) It also eliminates the problem that you can’t use a table prefix if you want to access other databases.

If there’s an easier solution, please let me know. Otherwise I’d be happy if this could be incorporated in a forthcoming release.

-Stefan

Last edited by dust (2005-12-30 20:54:38)

zem · 2005-12-30 22:14:43

If there’s an easier solution, please let me know.

There is: remove all the backticks entirely. That’s been done in the Crockery branch already. Backticks aren’t standard SQL, so they’d break Postgres etc.

dust · 2005-12-30 23:04:24

Well, I didn’t introduce them ;)
And while on 4.0.3 I have to use them to make the plugins fly. But thanks for the info, I’ll check whether I’m allowed to upgrade to the latest CVS.

Thanks again,
Stefan

Last edited by dust (2005-12-30 23:05:06)

zem · 2005-12-30 23:32:53

Crockery is experimental, don’t use it on a live site.

You could remove the backticks from txplib_db.php with a quick search and replace.

dust · 2005-12-30 23:50:32

Would “downgrading” txplib_db.php to version 4.0.2 do the trick, too, without breaking anything? The reason I ask is that my customer (read: the one who’s paying me to develop the plugins) want’s to run stable code and thus doesn’t want (or even allow) me to mess with TXP’s code. I could possibly talk them into replacing one part of a stable version with an older part of an older stable version, however … (Yup, I know that’s scary, but that’s the way they tick). Actually, I can’t replace or modify anyting in TXP itself. I have read/write access to the plugin cache directory only, that’s where I do the development. The web root and everything below is read-only for me.

Stefan

zem · 2005-12-31 00:33:55

Downgrading doesn’t always work. I haven’t tried 4.0.3 to 4.0.2. Back up first, if you go that route.

The other option would be to use safe_query() instead of the other safe_*() functions.

Sencer · 2005-12-31 14:20:22

The backticks were added in revision 833 which was well before 4.0.2 was released. They were added when people were reporting problems when choosing certain table-prefixes. The first impulse was to quote the table-names to prevent those errors, bt then we decided to simply limit the variety of available prefixes (so that they would work without quoting).

Given that the backticks/quoting doesn’t serve any other issue, simply remove them from your copy of txplib_db.

Textpattern CMS

Textpattern CMS support forum

#1 2005-12-30 20:13:45

[issue] 4.0.3 breaks MySQL access (external tables)

#2 2005-12-30 22:14:43

Re: [issue] 4.0.3 breaks MySQL access (external tables)

#3 2005-12-30 23:04:24

Re: [issue] 4.0.3 breaks MySQL access (external tables)

#4 2005-12-30 23:32:53

Re: [issue] 4.0.3 breaks MySQL access (external tables)

#5 2005-12-30 23:50:32

Re: [issue] 4.0.3 breaks MySQL access (external tables)

#6 2005-12-31 00:33:55

Re: [issue] 4.0.3 breaks MySQL access (external tables)

#7 2005-12-31 14:20:22

Re: [issue] 4.0.3 breaks MySQL access (external tables)

Board footer