Escape Character Overlap Between SQL and Python

Ignition
,
1

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

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

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.

2

Perhaps using a StringBuilder will work?

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

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.

4

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.

1 Like
5

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

6

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. :upside_down_face:

1 Like
7

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

1 Like