Perspective table edit example, please

[PGriffith]

@Transistor

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?

1. Yes, parameter substitution is 'safe' - the database will not allow arbitrary input values, only direct data types that it can substitute into the query.

2. 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.