prepared statement database creation?

[zombieJones]

How can I do that? I use prepared statements with mysql and mdb2 when I insert data, to avoid sql injection, but I can't find a way to do the same thing for creating a database.

[chazzy]

you're letting yoru app create its own database?

also, can you specify the programming language you're using?

[zombieJones]

Yes, is that a terrible idea? The site uses data from another site (Last.fm). It imports a number of weekly xml files and creates a table for each file, so each user gets his own database after I strip any quotes from the input and check that the username is valid. Each database usually has over 100 tables in it. Or I could use one big database with thousands and thousands of tables in it. Would that be the smarter and safer alternative? What if a hacker compromises the database by an injection or something? Maybe having many separate databases would limit the damage. Or maybe I should just backup the big database every day.
I don't know how web applications usually handle this sort of thing. I am a self-taught noob, so of course I am using PHP.

[chazzy]

alright now i atleast understand what you're trying to do.

prepared statements only work on CRUD operations (SELECT, INSERT, UPDATE, DELETE) because you bind parameters to values. you don't end up with that sort of structure in things like CREATE DATABASE or CREATE TABLE statements.

to be honest, my thought is that the structure you described is not a good idea. database structures generally require some kind of permanence. to be honest, i don't see how your approach will work against hackers. even if you are limiting their queries to the current DB by using a select, don't forget you can still do


select * from mysql.user;


when you select another DB. what you've done just tricks them a little bit, but they'll figure it out soon enough.

if all databases have the same 100 tables, then a single database w/ 100 tables should suffice. with proper indexing performance will be great, and with a good DB approach (which you're already showing you know by using prepared statements, rather than building your own SQL by hand) you'll do fine.

BTW, since you're using PHP, are you using PDO? http://us.php.net/pdo