Problem: My task is to create a database to hold information about various products and to create RESTful api to serve and manage this information. But the client doesn't know exactly what all information they will need on these products, so the database is likely to have new columns and tables added later on to accommodate new product properties. My question is about producing a database that will readily accept these changes and constructing queries that can fetch products securely based upon product properties that don't yet exist with little to no modification.
Proposed Solution: I have a test database setup with the following structure.
+------------------+
| item |
+----+------+------+
| id | name | cost |
+----+------+------+
| 0 | test | 50 |
+----+------+------+
+--------------+
| color |
+----+---------+
| id | val |
+----+---------+
| 0 | blue |
| 1 | purple |
+----+---------+
+--------------------+
| item_color |
+---------+----------+
| item_id | color_id |
+---------+----------+
| 0 | 0 |
| 0 | 1 |
+---------+----------+
The 'item' table will likely have columns added later and more junction tables will likely be added too.
A request to retrieve products would like http://www.example.com/api/products?color=purple&cost=50 With php I'm dynamically constructing prepared statements to retrieve the relevant products, hopefully without opening the door for sql injections. First I determine which properties are included in the 'item' table and which are in separate tables using the following functions:
function column_exists($column, $pdo) {
$statement = $pdo -> prepare("DESCRIBE item");
$statement -> execute();
$columns = $statement -> fetchAll(PDO::FETCH_COLUMN);
$column_exists = in_array($column, $columns);
return $column_exists;
}
function table_exists($table, $pdo) {
$statement = $pdo -> prepare("SHOW TABLES");
$statement -> execute();
$tables = $statement -> fetchAll();
$table_exists = in_array($table, $tables);
return $table_exists;
}
If the property is not found as a column in the 'item' table or as a table name, then an exception is thrown.
The prepared statement my code constructs would look like:
$sql = "SELECT * FROM item WHERE cost = :cost
AND id IN (SELECT item_id FROM item_color
WHERE color_id IN (SELECT id FROM color WHERE val = :color));";
And would be executed like so,
$statement = $pdo -> prepare($sql);
$statement -> execute(Array(":cost" => $cost, ":color" => $color));
What I want to know: Will I encounter major bottlenecks as the database grows and is accessed more frequently? Is my method of retrieval safe against 1st order sql injection attacks?
What I have done: I have read about rudimentary database design principals and basic sql injection attack/defense methods. I've tried to read about dynamically creating prepared statements, but the material I've found isn't what I'm looking for. I have tested my design against a basic Bobby Tables attack.
Why this question is relevant: The design principles I've read warn against trying to make database too flexible Those new to the field have no way to gauge how flexible is too flexible and could benefit from an analysis of this example. Also, it seems that prepared statements are only intended to be used as static templates. If I find this dynamic way to constructing them to be enticing then other newbies probably will too, so we need to know if we're creating a great security vulnerability. Lastly, I've asked these question together because the database design and the structure of the queries to be run against it are directly related.
Details: php v5.6.17 mysql v5.6.35
