# Perspective table edit example, please

**URL:** <https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [May 16, 2019, 9:36am UTC](https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050 "2019-05-16T09:36:23Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 16, 2019, 9:36am UTC](https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050/1 "2019-05-16T09:36:23Z")

</div>

Could someone outline the steps required to update the database bound to a table?

What I know so far:

- Drop a table component onto the perspective view.
- Bind the _data_ property to the datatable using a named query. (SELECT \* FROM , etc.)
- Enable editing on the _columns_ as required.
- On _cells | allowEditOn_ select click, double-click or long press as required.

I know that when Enter is pressed during edit that the _onEditCellCommit_ component event is fired. I know I now need to do two things:

1. Update the _Perspective_ table.
2. Update the database table.

Item 1 should be doable with

```auto
def runAction(self, event)
    ...
	self.props.data[event['row']][event['column']] = event['value']

```

but I get a message in the server log file saying

```auto
... ignition.gateway.datasource.BasicStreamingDataset object is unscriptable.

```

Questions:

1. Any idea what I’m doing wrong when I try to update the table?
2. How do I extract the database table ‘id’ field from my _Perspective_ table? (I’ll need it for the UPDATE SQL.)

Many thanks.

* * *

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 20, 2019, 4:45pm UTC](https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050/2 "2019-05-20T16:45:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 21, 2019, 1:38pm UTC](https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050/3 "2019-05-21T13:38:00Z")

</div>

Thank you very much for your answer.

Regarding the risk of SQL injection, can you comment on the following solution?

```auto
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?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 21, 2019, 4:58pm UTC](https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050/4 "2019-05-21T16:58:23Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [November 26, 2020, 5:20am UTC](https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050/5 "2020-11-26T05:20:24Z")

</div>

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:

```auto
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

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 26, 2021, 9:42am UTC](https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050/7 "2021-03-26T09:42:06Z")

</div>

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)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/8/a8dad23c33c7ee6c826e517da3979232914ee9a2.png)  
I have dataset a table conf. What shoup i do o use this code?  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/1/518f93a0d743d9c77f7a67ba2d9ed215cf2d7223.png)

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [March 30, 2021, 5:47pm UTC](https://forum.inductiveautomation.com/t/perspective-table-edit-example-please/25050/8 "2021-03-30T17:47:32Z")

</div>

Set your named query to return JSON

```auto
# 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

```