Yes you can… you have to use a combination of the two though. I like the .format() to do thing like this.
Example:
columnName= "binder_name"
query = "UPDATE binder_names SET {}=? WHERE id=?".format(columnName)
args = [newValue,id]
system.db.runPrepUpdate(query = query, args = args)
And if you want to do both the table name and the column name then you could do something like this:
tableName ="binder_names"
columnName = "binder_name"
query = "UPDATE {0} SET {1}=? WHERE id=?".format(tableName,columnName)
system.db.runPrepUpdate(query=query, args=args)
Make sure that you’re not accepting direct string input from users for the table or column names, though, or you’re opening yourself up to SQL injection as an attack vector.
I am getting the colName from the onCellEdit event on the power table and I am assigning the table name to a variable.
The user on changes the value of a cell.
Thanks for that!
Steven
@lrose, @bschroeder, @cmallonee, & @PGriffith
That's takes it down to just a few lines. Also, onCellEdit passes the colName & newValue. When I create the args list it use those values automatically.
I guess what I dont fully understand is why we use the format() for the instead if just including them as args?
I can see why y'all say use runPrepUpdate all the time. Much easier to keep up with.
Thanks for all the learning.
Steven
So If I wanted to make this more readable (for larger queries for example) I could do this?
'{first} {last}'.format(first='Hodor', last='Hodor!')
I see this is just using format() as a name placeholder. Makes much more since now.
Yes
I mean you could do it in one line if you wanted to:
system.db.runPrepUpdate(query="UPDATE {0} SET {1}=? WHERE id=?".format("binder_names","binder_name"),args=[newValue,id])
That is really just up to personal style.
Paul, I thought that was one of the benefits of using the runPrepUpdate and runPrepQuery scripts was it provides prepared statements which provide a layer of protection to SQL injection attacks. Is this not the case? I've never fully understood exactly how to achieve this, but is it as simple as placing single quotes around the question marks so if someone places a quotation mark it fails (or throws an error)?
Documentation indicates this, so if this isn't true, maybe a modification to this is warranted.
My understanding is that the statement is prepared for any value you pass via the ‘?’ and parameter syntax of runPrepUpdate. The potentially vulnerable part is the {0} and .format pieces. These aren’t handled any special way by runPrepUpdate because the query string is already constructed with potentially malicious SQL by the time runPrepUpdate starts.
That makes sense. So why not pass the table and column names as part of the arguments and just add more question marks to the sql query itself?
Someone else will have to confirm, but I don’t think you can send schema objects (tables, columns, etc.) as prepared values. That’s why QueryString is still a (discouraged) option in named queries. I’m sure @PGriffith will correct me if I’m wrong about this.
SQL will not allow you to send schema objects as parameters.
It is indeed the case assuming a hard coded query string. However, when you allow dynamic construction of the query string, then the risk for injection is still there.
In this case so long as the dynamic values are only supplied by the programmer, then the risk is still mitigated, thus @PGriffith's warning to not allow user input into the query string itself.