For 1, I wouldn’t update it directly. If you’re already populating the table with a query, then the easier way to update is just to fire the binding again - that way, you know that the data you’re displaying accurately reflects what’s in the database. To do that, use refreshBinding() on the component itself - ie, self.refreshBinding("props.data").
But, before you can do that, you’ll obviously need to update the actual data. Here things get a bit more complicated than Vision - since you can’t write a dynamic SQL query (well, you shouldn’t if using named queries [1]). You can either A: write a series of named queries for each potential DB column that needs to be updated, and select between them (probably dynamically, based on a column name : named query name mapping. That’s fine, but is understandably a bit of a maintenance headache. The alternate option is to use scripting - don’t perform wide-open column replacement, but construct your SQL dynamically from a limited list of possible column names and run it your update entirely through scripting.
As for fetching the ID: You may find it’s easier to work with your query results in Perspective if you change the return format on your query binding to ‘Document’ (meaning, essentially, raw JSON) rather than ‘Dataset’, since datasets in Perspective are deliberately opaque and only partially supported.
[1] you can write dynamic SQL using named queries (using the ‘query string’ parameter type, but doing so means reintroducing the security problem named queries exist to avoid…so, in general, please don’t.
Regarding the risk of SQL injection, can you comment on the following solution?
def runAction(self, event):
"""
Method that will run whenever the selected event fires.
Arguments:
self: A reference to the component that is invoking this function.
event: An object that holds information about the selected event type
"""
r = event['row']
c = event['column']
v = event['value']
# Important: this next line doesn't work if the table binding is returned as a dataset.
# You must select JSON (if using a named query). 'Document' format
# may work otherwise (but is untested by this user).
id = self.props.data[r].id
# We're going to sanitize the 'column' name to prevent SQL injection.
fields = ['lan_ip', 'router_ip', 'status']
for f in fields:
if f == c:
dbConn = "dbLAN"
# -- SQL example
# UPDATE devices
# SET status = 'Online'
# WHERE id = 122
# Use %s Python substitution for the field name. Use ? substitution for the value and db-row number.
system.db.runPrepUpdate("UPDATE devices SET %s = ? WHERE id=?" % (c), [v, id], dbConn)
# Rather than write back to the table, we'll refresh the binding to show what's actually in the db table.
self.refreshBinding("props.data")
break # Stop looking.
Q1. As you can see, we’re sanitizing the column name by checking it against a dictionary. My understanding is that the runPrepUpdate function will sanitize the ‘?’ data returned by the user. Is this correct?
Q2. Is it really necessary to sanitize the column name? Is this just to protect against interference with the HTTP POST variables?
Yes, parameter substitution is 'safe' - the database will not allow arbitrary input values, only direct data types that it can substitute into the query.
Yes, to be genuinely safe, you must know what is being populated into your query. Take the example code you have - if you had a direct substitution of event['column'] into system.db.runPrepUpdate("UPDATE devices SET %s = ? WHERE id=?" % (c), [v, id], dbConn) - if I were a malicious actor, I could figure out a way to send data over the websocket channel from the browser session to the gateway - and send a payload with event['column'] something like 1=1 WHERE 1=0; TRUNCATE TABLE PRODUCTION_RECORDS; --, which then gets dropped into your sql query exactly: system.db.runPrepUpdate("UPDATE devices SET 1=1 WHERE 1=0; TRUNCATE TABLE PRODUCTION_RECORDS; -- = ? WHERE id=?" % (c), [v, id], dbConn).
There is no way to guarantee the integrity of clients - you really couldn't trust them in Vision, either (hence the original introduction of named queries) but there's at least a more significant barrier to entry. In the web world, there are literally hundreds of tools expressly designed to allow malicious data injection - trusting user input implicitly is never a good idea.
Don't use id as a variable name, it's reserved in Python and when you overwrite those it can cause issues. When variable names get syntax highlighted that should set off alarm bells
As you can see, we’re sanitizing the column name by checking it against a dictionary
You're actually checking against an array and iterating over that but you're right, this is the job for a dictionary as the for f in fields:; if f == c:; break logic has already been implemented and optimized by the interpreter... and it's easier to read. I'd probably do something like:
row, col, val = event['row'], event['column'], event['value']
# this next line doesn't work if the table binding is returned as a dataset.
# You must select JSON (if using a named query)
newID = self.props.data[row].id
# translate pretty names to the actual SQL columns. also prevent SQL injection
colToSQLCol = {
'Lan IP' : 'lan_ip'
, 'Router IP' : 'router_ip'
, 'Status' : 'status'
}
# Use %s Python substitution for the field name. Use ? substitution for the value and db-row number.
# curse the implementation of prepared statements
query = '''
UPDATE devices
SET %s = ?
WHERE id = ?
''' % colToSQLCol[col]
args = [val, newID]
rowsAffected = system.db.runPrepUpdate(query, args, 'dbLAN')
if rowsAffected == 0:
system.logger.getLogger('a logger').error('no updates made: %s with args %s' % (query, args))
# refresh the binding to show what's actually in the db table
self.refreshBinding("props.data")
Anyway reviving this because it's a distraction from figuring out why every edit I make on the Perspective table gets immediately reverted and what in the simulated world is going on with editing the dang boolean columns and this popped up in my searching
Can i get some help? I am acomoding the code to may table.
I am accommodating the code to my table.
DB - Tallerelectrico
table - articulos
I what to modify the stock (int)
row, col, val = event['row'], event['column'], event['value']
# this next line doesn't work if the table binding is returned as a dataset.
# You must select JSON (if using a named query)
newID = self.props.data[row].id
# translate pretty names to the actual SQL columns. also prevent SQL injection
colToSQLCol = {
'stock': 'stock'
}
# Use %s Python substitution for the field name. Use ? substitution for the value and
db-row number.
# curse the implementation of prepared statements
query = '''
UPDATE articulos
SET %s = ?
WHERE id = ?
''' % colToSQLCol[col]
args = [val, newID]
rowsAffected = system.db.runPrepUpdate(query, args, 'Tallerelectrico')
if rowsAffected == 0:
system.logger.getLogger('a logger').error('no updates made: %s with args %s' % (query,
args))
# refresh the binding to show what's actually in the db table
self.refreshBinding("props.data")
# this next line doesn't work if the table binding is returned as a dataset.
# You must select JSON (if using a named query)
newID = self.props.data[row].id
