October 29th, 2013, 03:42 PM
Sql variable column
I want to update the value of a select group of columns. I did this:
html_escape is a custom function that escapes 'dangerous character' etc.
$var_column_stmt = $db->prepare('
SET :var_column = :var_column_value
WHERE id = :id
':var_column' => html_escape($var_column)
, ':var_column_value' => $stats['value']
, ':id' => html_escape($_GET['id'])
$db is the connection to the database.
:id and :var_column get the value of a variable that has been passed from page to page by URL (with the $_GET statement)
October 29th, 2013, 06:32 PM
You can't use this for the column name substitution. The escaping that the prepare() function does puts single-quotes around strings, and that's not valid SQL syntax. You have to set the column names manually.
October 29th, 2013, 08:53 PM
what you're trying to do there is a major security risk or at least a very bad idea. If this worked, any user could set the value of any column. That's certainly something you do not want. Just imagine somebody messing with the primary key.
Apart from that, I have no idea what the HTML escaping is supposed to do. As the name suggests, it's for HTML. It has nothing to do with database queries.
What exactly are you trying to do? Why you do want to set the columns dynamically?
October 31st, 2013, 04:00 PM
I'm trying to change some aspects of an object. I have a couple of tables like:
categories (a list of the categories of the mods, I also have subdivided the categories in subcategories by adding a column (subcategories) and referencing to the primary key in the same table),
objects (with those categories, this table contains the objects that will be 'modded' and the 'mods' themselves) and
owned_objects (with those objects and POSSIBLE mods that the user owns.)
and I want to update one of the many columns that represent the mods of the table owned_objects. But the problem is that I don't know which column I have to update.
I get it if you don't know what I mean, I'm not that good in explaining
October 31st, 2013, 06:34 PM
We know exactly what you mean, and we've told you why it won't work like the way that you're trying to do it.
The prepare() statement is not meant to be used with dynamic column/table names. It just doesn't work that way.
To get around that I'd suggest having a pre-defined set of valid column names, and ensure that the column that's requested is in that list before you do any processing. That way you can insert the table name into the query string before the call to prepare() and you'll know that it's valid - and you won't need to worry about how prepare() does it's escaping.
October 31st, 2013, 07:58 PM
I'd question the whole approach.
First of all, it looks like you're using the GET method to do an update. This is semantically wrong and will lead to all kinds of ugly problems (accidental updates through reloading etc.). The GET method is strictly for fetching resources, nothing else (hence the name). If you wanna change or add a resource, you need to use POST.
Secondly, it doesn't make sense to specify column names in the URL. The way updates are usually done is you send a form (with POST!) that contains a bunch of key-value pairs. Then you go through the properties of the object and change them according to the specified values, possibly skipping empty values. This automatically gives you a whitelist of properties that may be changed and lets you validate the values.
November 1st, 2013, 04:46 PM
ok so say it if I'm wrong, I have to turn the GET into a POST and control it with an array with the columns and a foreach. But I don't really get the second part with the key-value I think... Also I think I will make it a bit more compact. I think I will just put every property in a container so all the mods can be specified at once...
November 1st, 2013, 04:55 PM
Well, just what a normal input element does:
Originally Posted by derplumo
myname is the name (or key), and myvalue is the value.
<input type="text" name="myname" value="myvalue" />
November 2nd, 2013, 08:38 AM
ok, I was thinking too difficult
never mind then
November 13th, 2013, 03:10 PM
Is there a possibility to replace the form by something else but still use the POST method, this because it may be a cleaner code...
November 13th, 2013, 03:40 PM
You mean you don't like all the <form> elements?
If you wanna stick to the current user interface, use forms. If you wanna change the user interface and do the actions without loading a new page, use Ajax.
November 14th, 2013, 03:47 PM
Let's keep it simple and faster (because the loading time may be longer if I have to load all the data and then it takes longer, right?)
Thanks for the quick reaction