bindParam works with values, but does not work with names, how to fix it?

$cache = XXXX->prepare('ALTER TABLE XXX ADD :name varchar(2) NOT NULL;'); $cache->bindParam(':name', $name, PDO::PARAM_STR); $cache->execute();
  • one
    And will not work. SQL does not provide for the ability to pass elements of DDL statements (including alter table) as parameters. substitute the value directly into the text - Mike
  • Yeah from the $ name itself, do you want an injection? - Denis Kotlyarov
  • Otherwise, not as in your formulation of the problem. field names cannot contain quotes and semicolons, so just remove them from the string and the injection is excluded. - Mike
  • And by the way, note that it is not physically possible to add a NOT NULL column to a table that already has records. - Mike

1 answer 1

Database ABC:

The database structure changes only when the site is updated, and never on the fly.

If you suddenly need to add fields to the table from user input - this means that the database is not designed correctly

  • This is not an answer. An example is a table. It has lines (users) and there are columns (corn, tomatoes). It is required to add goods to the user (potatoes) and later this field will be either +1 or -1 (the amount of goods purchased). Create another table to store id and value in it? and if there will be goods for a thousand, what will happen to mysql itself which makes it easier to work with one line than to look for a hundred with the name of the product potato and its quantity. - Denis Kotlyarov
  • @DenisKotlyarov I probably agree with Iptev "The database was designed incorrectly ." To add a column to each torvar is not done in relational databases. A reference is made to the types of goods, where the potatoes are assigned an id and a table is made of the form "user, type of goods, quantity" - Mike
  • But the question is, if you want to get the goods pieces 20? Offer to create mysql requests for 20 pieces? It seems to me easier to get 1 line containing all that I need and? - Denis Kotlyarov
  • @DenisKotlyarov Get one line of course easier. but you don’t need to do 20 requests, you get a list in one request. And you said that you will have goods for a thousand. 1000 columns, even if the database allows - this is a disaster. Even if they are NULL, the record header should contain 125 bytes of table of contents and that the saddest thing to add is that every eighth column will require lengthening all records, which will entail mass relocation of records to other blocks, which will cause major I / O degradation - Mike
  • @DenisKotlyarov And creating an index on which you can quickly find, for example, all entries in which there is such a product will be impossible, which will enumerate the entire table for any search for something - Mike