#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8

    Sql variable column


    hi,

    I want to update the value of a select group of columns. I did this:

    PHP Code:
    $var_column_stmt $db->prepare('
        UPDATE table
        SET :var_column = :var_column_value
        WHERE id = :id    
    '
    );
    $update_mod_stmt->execute(array(
        
    ':var_column' => html_escape($var_column)
        , 
    ':var_column_value' => $stats['value']
        , 
    ':id' => html_escape($_GET['id'])
    )); 
    html_escape is a custom function that escapes 'dangerous character' etc.

    $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)
  2. #2
  3. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,112
    Rep Power
    1990
    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.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    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?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    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
  8. #5
  9. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,112
    Rep Power
    1990
    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.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    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...
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by derplumo
    But I don't really get the second part with the key-value I think...
    Well, just what a normal input element does:

    Code:
    <input type="text" name="myname" value="myvalue" />
    myname is the name (or key), and myvalue is the value.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    ok, I was thinking too difficult

    never mind then
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    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...
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    You mean you don't like all the <form> elements?

    Of course there's also Ajax for doing requests with JavaScript. However, this makes things more complicated and obviously requires JavaScript to be enabled in the browser (which isn't always the case). Using it only to get rid of the forms would be a poor choice.

    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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  22. #12
  23. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    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

IMN logo majestic logo threadwatch logo seochat tools logo