1

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?

Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
  • 3
    By removing anything that's not a-z, for example. Regexp them out. – Sami Kuhmonen Jul 09 '16 at 08:47
  • @SamiKuhmonen that's not the best thing to do. You could run into conflicts with similiar names, i.e. `$Bob` and `§Bob` would become the same thing, no matter if you remove or replace the special character. When the user signs up, he gets an ID I Assume - name the database `database_{userid}` – dognose Jul 09 '16 at 09:06
  • @dognose Of course you need other checks, but that's a given: who says you never get two John Smiths? Usernames may be unique, but they would already be without special chars. Users' names aren't unique. – Sami Kuhmonen Jul 09 '16 at 09:11
  • @dognose feel free to post that as an answer – Mawg says reinstate Monica Jul 09 '16 at 09:28
  • Lolx I just noticed that I asked basically the same question almost 6 years back. Plus ca change ... – Mawg says reinstate Monica Jul 14 '16 at 09:19

3 Answers3

1

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, '-_', '+/'));
Amit Ray
  • 3,445
  • 2
  • 19
  • 35
1

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?

dognose
  • 20,360
  • 9
  • 61
  • 107
0

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'
Martin Hučko
  • 791
  • 5
  • 16
  • Martin, are you telling me that using prepared statemnts will automatically strip any characters which MySql does not recgard as valid in a database name? if so, can you provide a reference & I will award you the answer. Thanks. – Mawg says reinstate Monica Jul 11 '16 at 07:42
  • 1
    Take a look at [Are PDO prepared statements sufficient to prevent SQL injection?](http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection). It will not strip any characters, but there is no need to do it. – Martin Hučko Jul 11 '16 at 07:48
  • I will give it a try this evening – Mawg says reinstate Monica Jul 11 '16 at 11:45