How to protect variable table names against SQL injection in PHP

Question

I am building a web app which relies heavily on a database. Here is an example of the type of query I use a lot:

CREATE TABLE item$userenteredname$username

Basically each time a create a new item, there is a table which stores info for every time something is added. So I need the table names to remain the same.

I recently spent quite a while updating my code to use PDO. I understand how to prepare statements and bind values, but you can't do this with table names. Haven't been able to find a proper answer to my question, which to clarify is...

How can I sanitise user input against sql injection when I can't use prepare's or mysql_real_escape_string because the variable is in a table name?

That is a stupid design. Better spend some time creating proper architecture than dealing with thousands of tables — xzag, Apr 18 '14 at 09:39
If you have hyper variable table names, your database design is wrong. — deceze, Apr 18 '14 at 09:40
go to this link you will get idea http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Ferrakkem Bhuiyan, Apr 18 '14 at 09:41
@Shurik, that might be the case, but you could be a little more constructive - we all have to learn at some point. **user1973975**, Shurik has a point in that your architecture is likely wrong. If you're stored custom data for each user consider a two-table architecture where one provides Keys (or custom column names) and the other stores values (user custom data - specific to a key). — Jon Bellamy, Apr 18 '14 at 09:42
Ok I think you guys have a good point. Basically this is the first thing I've ever coded and I'm only just starting out and all I want to do is finish a project. The app is done and working but now I am adding security, so I'm just desperate to finish it. Next time I'll design the database more carefully. — user1973975, Apr 18 '14 at 10:06
Ok I just decided to redesign the database. Thanks for your suggestions, I reckon once I do this the application will be much better. As you said, having loads of tables would be impractical. — user1973975, Apr 18 '14 at 14:35

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

My strategy for this use case would be to strip out non-alphanumeric characters.

    $usereneteredname = preg_replace('/[^0-9a-zA-Z_]/', '', $usereneteredname);
    $username = preg_replace('/[^0-9a-zA-Z_]/', '', $username);

    // then "CREATE TABLE item$userenteredname$username"

This strategy is called whitelisting. That preg_replace call will replace anything that isn't 0-9a-zA-Z_.

Further considerations:

You may also wish to validate the string lengths after the output, and make sure you are operating on a string not an array.

How to protect variable table names against SQL injection in PHP

1 Answers1

Further considerations: