Update script not erroring out, but not working either?

Ignition
1

I have a table in SQL that has multiple columns that multiple managers will check and sign off for their department, but since managers cover for each other I have it set so that any manager can sign for any department. I have a drop down that contains the column headings in the table and am passing those parameters into the statement with this script:


# un = User Name
# T = Timestamp
# SA = Signed as - What department/role they're signing for
# ST = Signtime column in the SQL database
# r= Request column in SQL
un = event.source.parent.getComponent('Username').text
t = event.source.parent.getComponent('Label 1').text
sa = event.source.parent.getComponent('Dropdown 1').selectedLabel
st = event.source.parent.getComponent('Dropdown 1').selectedStringValue
r = event.source.parent.getComponent('Dropdown').selectedStringValue
system.db.runPrepUpdate("Update Part_reg_signoff Set ?=?,?=? where request = ?",[sa,un,st,t,r])
system.gui.messageBox("I did : Update Part_reg_signoff Set " + sa + "= " + un + ", " +st + " = " +t +" where request = " +r )

I added the message box as a way to verify the statement was correct. If I take the statement that it produces, and paste it into SSMS it will update the table fine, I get no errors or issues and everything seems to pass fine in Ignition, but it’s not updating the table in SQL. What am I missing?

Edit: All columns are nvarchar in SQL

2

Is runPrepUpdate returning a non-zero value?

1 Like
3

You can’t use ‘?’ parameter substitution to supply column names (or any other identifiers). That is structure, not data.

2 Likes
4

Sorry man I’m not sure how to check that? I added in the message box at the end to I think give me the raw statement itself

Untitled

This doesn’t work in Ignition but this statement does work in SSMS:

Untitled2
Untitled21256×66 26.6 KB

This one does work…so not sure what to do

5

You’ll have to use string substitution for your column names. You can only use ‘?’ substitution for actual data.

1 Like
6

Sounds good, but I apologize I’m not entirely sure how to make that happen. I have 5 columns, So I just made a drop down with those values in it so that the user can select which department they want to sign for. How would I go about doing a string substitution for that?

7

Something like this:

system.db.runPrepUpdate("Update Part_reg_signoff Set %s=?,%s=? where request = ?" % (sa, st),[un,t,r])

Mind you, this is how people get SQL injection vulnerabilities.

3 Likes
8

That worked, You're awesome! Also, thanks @Kevin.Herron

9

Let me clarify: ? parameters are NOT substituted as strings into the SQL that goes to the DB. They are handed off to the DB in an efficient binary side-channel that doesn’t participate in interpretation of the SQL syntax. Quoting and most other type conversions are omitted because the DB doesn’t need them in binary. You can only do this with actual data. Column names are not data.

3 Likes