CODEWITHSUNDEEP
mysqlalias
ComputerUser
ComputerUser

Reputation: 4878

MySQL - Aliasing the database name?

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?

Upvotes: 2

Views: 3010

Answers (2)

piotrp
piotrp

Reputation: 3864

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.

Upvotes: 1

mvds
mvds

Reputation: 47034

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.

Upvotes: 0

Related Questions