# Escape Character Overlap Between SQL and Python

**URL:** <https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933>\
**Category:** Ignition\
**Tags:** scripting, ignition81\
**Created:** [September 9, 2022, 5:54pm UTC](https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933 "2022-09-09T17:54:51Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 9, 2022, 5:54pm UTC](https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933/1 "2022-09-09T17:54:51Z")

</div>

I have a weird edge case where I am passing a string into a named query for a `LIKE` where clause. In a certain case, the string ends in an underscore. This must be escaped with a backslash in SQL in order for it to be used literally.

Naturally, I try to add the backslash to the string in my script

```auto
string = 'text\_'
print string
>> 'text\_'

```

However, when you return it from a function, python adds an escape for the backslash escape intended for the query

```auto
def getString():
	string = 'text\_'
	return string
getString()
>> 'text\\_'

```

This doesn’t work as the python escape character gets passed to the query which breaks it.

---

<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:** [September 9, 2022, 6:10pm UTC](https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933/2 "2022-09-09T18:10:39Z")

</div>

Perhaps using a StringBuilder will work?

```auto
from java.lang import StringBuilder
def getString():
    sb = StringBuilder()
    sb.append('text\_')
    return sb.toString()
```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 9, 2022, 6:23pm UTC](https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933/3 "2022-09-09T18:23:18Z")

</div>

Hmm, I may be mistaken. I believe there was a different problem, because it works now despite the python escape character. Seeing the extra backslash in the console just confused me, it seems to not affect the query.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 9, 2022, 6:43pm UTC](https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933/4 "2022-09-09T18:43:23Z")

</div>

Why are you building values in SQL manually? Use `?` substitution in a Prep query or `:param` substitution in a named query. No escaping at all.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 9, 2022, 6:45pm UTC](https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933/5 "2022-09-09T18:45:18Z")

</div>

To do a like query with a named query param you can do `LIKE CONCAT('%',:myStringParam,'%')` (adjust your % as needed).

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 9, 2022, 7:05pm UTC](https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933/6 "2022-09-09T19:05:17Z")

</div>

> [@bkarabinchak.psi](#):
>
> LIKE CONCAT('%',:myStringParam,'%')

Yes, I'm aware of this, and in fact, I am doing this already. I'm using multiple parameters that are being concatenated inside the query. One of these parameters can end in an underscore or may not. I'm dealing with a very complex legacy file naming convention with several edge cases.

I guess somehow I got so deep, that I didn't think to add it to the query concat? Beats me, but it does seem to work that way, lol. 🙃

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 9, 2022, 7:09pm UTC](https://forum.inductiveautomation.com/t/escape-character-overlap-between-sql-and-python/63933/7 "2022-09-09T19:09:09Z")

</div>

Haha I get it sometimes you get lost in the weeds.