MySQL - Aliasing the database name?

Question

I have a systematic problem that I don't have the time or budget to fix and I was hoping that there was a mysql hack I could deploy which would save my little world from impending doom.

The creators of the system I now administrate didn't realise that a database name could contain characters other than letters, numbers and underscores. As a result queries look like this.

SELECT * FROM {$db}.settings

If the database name contains something non-standard... like a dash. The world ends and all queries break.

The db prefix is there because this system used-to-but-doesn't-now run a multi-site feature.

You would think that I could fix this with..

$db = "`{$db}`";

If only.. you see it turns out a load of javascript functionality sends around this database name to keep track of which site to update. By adding these quotes, you break all of that as instead of getting databasename, it is getting

`databasename`

So... why don't I just set the database name to something that is compatible.. this system ships to external servers where I have no control over the database name...

The pain...

Is there a way to alias the database name in a preliminary query which will then affect all subsequent queries. Basically set a constant?

Something like that would then work on all subsequent queries. Something like:

set `$db` AS database

I can just set the $db variable to database and it should run.. if this is possible?

piotrp · Accepted Answer · 2011-08-25 13:14:56Z

1

If you operate always on one database ($db doesn't change after you connect to the database), then just after connecting issue

USE `$db`;

and remove {$db}. prefix from all queries. USE query sets the active (default) database for future queries in current connection.

You didn't tell what programming language you are using, so I will assume PHP. Usually you can set default database while establishing connection (mysqli_connect(), $dbname parameter). Also, there may be a function like mysqli_select_db() that you can call after you connect.

edited Aug 25, 2011 at 13:14

answered Aug 25, 2011 at 13:07

piotrp

3,9841 gold badge26 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mvds · Accepted Answer · 2011-08-25 13:14:02Z

0

I would fix the queries using something like:

sed -e "s/{\$db}/\`{\$db}\`/g" -i * */* */*/* */*/*/*

with a big warning to test this first, because this may mess up a lot. Especially check if your sed's -i option needs a suffix or not.

answered Aug 25, 2011 at 13:14

mvds

47.4k8 gold badges104 silver badges113 bronze badges

Collectives™ on Stack Overflow

MySQL - Aliasing the database name?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related