Escape Character Overlap Between SQL and Python

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.

Perhaps using a StringBuilder will work?

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

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.

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

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

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

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

1 Like