Demi Demi - 10 months ago 61
MySQL Question

How can I escape special characters in a PDO connection string?

I am using the PDO extension to connect to a MariaDB database from PHP. I would like to know how to escape special characters for use in the connection string.

The obvious attempts (URL-encoding, enclosing in single quotes) all fail.

I currently don't need to connect to e.g. a strangely-named database, but I would like to know how in case I do.

Specifically: Suppose I have the following PHP code

$username = 'some user';
$password = 'some password';
$host = '';
$port = 1 << 16;
$dbname= 'alpha;a=b';
$PDO = new PDO("mysql:host=$host;port=$port;dbname=$dbname",
$username, $password, []);

This doesn't work because the semicolon in
is interpreted as a metacharacter. I would like to know how to escape this character.

Answer Source

If for some, strange reason you need to do this, don't use the dbname parameter in the dns. Instead, use your database driver to select it for you:

$dbh->query('use "newdatabase"'); // for mysql.

This way, you have the option to enclose the database name with ", so you can name it whatever the driver allows. For mysql it is the " character in this specific scenario, but that does not mean its different for postgresql.

But this method is not bulletproof, it only allows the PDO library to parse the DSN as normal as I don't expect the PDO library to cope with this unusual situation.