Handling Single Quotes in Stored Procedure Parameters for PX Policies

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

This article outlines the method for passing string values containing single quotes to stored procedures when configured within a PX policy. In environments where PX policies execute SQL commands, special characters must be properly escaped to prevent syntax errors.

Environment

Identity Manager 14.5 & 15

Cause

Stored procedures defined in PX policies often wrap input values in single quotes. If the input data itself contains a single quote, the SQL interpreter treats the character as a string delimiter, causing an execution error.

Resolution

1. Understand SQL Escaping: Because PX policies execute native SQL, you must apply the standard escape character for your specific database engine (e.g., SQL Server, Oracle).

2. Apply Escape Characters: To include a single quote within a string, use double single quotes ('') to escape the character.

   • Example: To pass the value O'Reilly to a stored procedure, format the input as O''Reilly.

3. Verify Syntax: Ensure the PX policy expression properly concatenates the input parameters. If the policy uses variable substitution, verify that the resulting SQL string is correctly formatted with the escaped quotes before execution.

Additional Information

To speak with a customer representative or a Support Engineer see Contact Support. Scroll to the bottom of the page and click on your respective region.