Search code examples
postgresqlcoldfusionescapingrailocfml

How to update a record with literal percent literal (%) in PostgreSQL without saving it as "\%"

I need to update a record, which contains literal percent signs, using PostgreSQL in Railo. The query looks like

<cfquery>
update foo set bar = 'string with % in it %'
</cfQuery>

It throws error as ColdFusion normally interprets it as a wildcard character. I can escape it using the following query.

<cfquery>
update foo set bar = 'string with escaped \% in it \%'
</cfQuery>

However, the record now contains "\%" in the database and will be displayed on the page as "\%".

I found a documentation with an example of escaping percent sign in a SELECT. But it does not work for me: syntax error at or near "ESCAPE".

SELECT emp_discount 
FROM Benefits 
WHERE emp_discount LIKE '10\%'  
ESCAPE '\';

Is there a better to achieve the same goal? The underlining database is PostgreSQL. Thanks!

Solution

  • Queryparameters escape special characters. Yet another reason to use them.