How to Escape Special Characters in PostgreSQL LIKE Patterns?

Escaping Special Characters in PostgreSQL LIKE Patterns

Escaping special characters in LIKE patterns is crucial to ensure accurate matching. For instance, consider a scenario where you're searching for rows where the name column begins with a user-provided string, such as "rob%". However, if the user input includes special characters like "_," it will result in false positives.

Escaping Mechanisms

PostgreSQL allows you to escape special characters using the backslash () or a user-defined escape character specified with the ESCAPE clause. To match a special character literally, you must escape it twice.

Example

To match "rob_" literally, you would use the following LIKE pattern:

WHERE name LIKE 'rob^^%'

Alternatively, you can use an escape clause and specify an alternative escape character:

WHERE name LIKE 'rob_%node1^^node2.uucp@%' ESCAPE '^'

This will match "john%node1^node2.uccp@" followed by any characters.

Considerations

• The default escape character is the backslash, but it's used for other purposes when standard_conforming_strings is OFF.
• Escaping special characters also applies to dynamic SQL statements constructed from user input, in which case it should be performed server-side to avoid SQL injection.
• Using placeholders for variable substitution ensures both accurate matching and security.

Generic SQL Statement

Here's a generic SQL statement that can be used with or without standard_conforming_strings ON, using server-side escape character replacement:

SELECT * FROM USERS WHERE name LIKE replace(replace(replace(,'^','^^'),'%','^%'),'_','^_') ||'%' ESCAPE '^'

The above is the detailed content of How to Escape Special Characters in PostgreSQL LIKE Patterns?. For more information, please follow other related articles on the PHP Chinese website!