# Passing different datatypes tp a named query

**URL:** <https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067>\
**Category:** Ignition\
**Created:** [October 27, 2020, 8:10pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067 "2020-10-27T20:10:54Z")\
**Posts on this page:** 13\
**Page:** 2

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [October 28, 2020, 5:47pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/21 "2020-10-28T17:47:22Z")

</div>

Yes you can… you have to use a combination of the two though. I like the .format() to do thing like this.

Example:

```auto
columnName= "binder_name"
query = "UPDATE binder_names SET {}=? WHERE id=?".format(columnName)
args = [newValue,id]
system.db.runPrepUpdate(query = query, args = args)

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 28, 2020, 5:56pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/22 "2020-10-28T17:56:45Z")

</div>

And if you want to do both the table name and the column name then you could do something like this:

```auto
tableName ="binder_names"
columnName = "binder_name"
query = "UPDATE {0} SET {1}=? WHERE id=?".format(tableName,columnName)
system.db.runPrepUpdate(query=query, args=args)

```

---

<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:** [October 28, 2020, 6:08pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/23 "2020-10-28T18:08:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 6:22pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/24 "2020-10-28T18:22:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 6:36pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/25 "2020-10-28T18:36:10Z")

</div>

@lrose, @bschroeder, @cmallonee, & @paul-griffith

> [@lrose](#):
>
> And if you want to do both the table name and the column name then you could do something like this:
> 
> ```auto
> tableName ="binder_names"
> columnName = "binder_name"
> query = "UPDATE {0} SET {1}=? WHERE id=?".format(tableName,columnName)
> system.db.runPrepUpdate(query=query, args=args)
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 6:47pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/26 "2020-10-28T18:47:37Z")

</div>

So If I wanted to make this more readable (for larger queries for example) I could do this?

```auto
'{first} {last}'.format(first='Hodor', last='Hodor!')

```

I see this is just using format() as a name placeholder. Makes much more since now.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 28, 2020, 6:54pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/27 "2020-10-28T18:54:53Z")

</div>

> [@steven.cox](#):
>
> So If I wanted to make this more readable (for larger queries for example) I could do this?
> 
> ```auto
> '{first} {last}'.format(first='Hodor', last='Hodor!')
> 
> ```
> 
> I see this is just using format() as a name placeholder. Makes much more since now.

Yes

> [@steven.cox](#):
>
> 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 mean you could do it in one line if you wanted to:

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

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 7:00pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/28 "2020-10-28T19:00:15Z")

</div>

@lrose,

> [@lrose](#):
>
> I mean you could do it in one line if you wanted to:
> 
> ```auto
> 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.

Awesome!

Thanks for the help!

Steven

---

<div class="post-metadata">

**Author:** ![Herbie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/herbie/32/65288_2.png) [@Herbie](https://forum.inductiveautomation.com/u/Herbie)\
**Post date:** [November 2, 2020, 5:31pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/29 "2020-11-02T17:31:07Z")

</div>

> [@paul-griffith](#):
>
> 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.

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](https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepUpdate) indicates this, so if this isn't true, maybe a modification to this is warranted.

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [November 2, 2020, 5:43pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/30 "2020-11-02T17:43:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Herbie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/herbie/32/65288_2.png) [@Herbie](https://forum.inductiveautomation.com/u/Herbie)\
**Post date:** [November 2, 2020, 5:46pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/31 "2020-11-02T17:46:22Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [November 2, 2020, 5:49pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/32 "2020-11-02T17:49:42Z")

</div>

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 @paul-griffith will correct me if I’m wrong about this.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [November 2, 2020, 6:10pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/33 "2020-11-02T18:10:57Z")

</div>

> [@zacht](#):
>
> Someone else will have to confirm, but I don’t think you can send schema objects (tables, columns, etc.) as prepared values.

SQL will not allow you to send schema objects as parameters.

> [@Herbie](#):
>
> 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?

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 @paul-griffith's warning to not allow user input into the query string itself.

[Previous page](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067.md?page=1)