CODEWITHSUNDEEP
phpmysql
Mawg
Mawg

Reputation: 40140

How to create a valid MYSql database name from a string?

When a new user registers, I want to use PHP to create a MySql datbase for them.

However, some charatcers in the user name, might not be suitable (consider O'reilly).

How can I "sanitize" the username to cerate a valid MySql databse name?

[Update] I could uase @dognose suggestion and user the uer Id in the db name, thus havonf db_1, db_2, etc

That certainly solves the special character problem, but it makes it slighly more difficult for me to debug than haviong a human readable name.

Not too much, though. And it solved the duplciate name problem.

What do others do?

Upvotes: 1

Views: 357

Answers (3)

dognose
dognose

Reputation: 20889

As i suggestet in the comment, using database_{userid} is the easiest and "savest" way.

To generate easier to read db-names, you can combine both approaches mentioned:

1.) First, remove any special characters from the username, replace them with "_". (For readability).

2.) Add the actual ID at the end, seperated with an underscore (for ensured uniqueness):

id | username | dbname
1    Bob$       bob__1
2    Bob§       bob__2
3    JohnX      johnx_3      
4    ABC        abc_4
5    Test       test_5

But keep in mind that this will cause different sortings of the database-list - and if there is a collision of "Usernames" you have to lookup the id anyway to find the right "Bob".

Side node: Depending on what kind of service you are offering: Maybe you should prefer a multi-tenant database over multiple databases?

Upvotes: 1

Amit Ray
Amit Ray

Reputation: 3485

I would suggest you to take a different approach. First you can sanitize the user input like

$username = preg_replace("/[^a-zA-Z]+/", "", $username);

and then you can append the base64 encode email provided and the lastinert id. Emails are unique so no need. It will look like

$userdb = $username.'_'.base64_encode($email).'_'.($db->lastInsertId());

you will get something like this which will always be unique

smith_YWRtaW5AYWRtaW4uY29t_234

EDIT NOTE:

In some base64 encode strings you may get = or == signs at the end. To avoid that you can do something like this

$email = strtr(rtrim(base64_encode($email), '='), '+/', '-_'); and then you can create the database.

Note that to decode that email you will need this

$email = base64_decode(strtr($encodedemail, '-_', '+/'));

Upvotes: 1

Martin Hučko
Martin Hučko

Reputation: 791

Why you don't want these characters in the name?

You don't have to sanitize the input, instead do not allow these data to be used in your SQL statement. For example use prepared statements (MySQLi, PDO, etc.).

EDIT:

But if you still want to "sanitize" the username and remove these characters, regexp them out as mentioned in a comment:

'/[^a-z_\-0-9]/i'

Upvotes: 0

Related Questions