To secure SqlExec implementations against SQL injection, you must use bind variables instead of string concatenation. SqlExec is a powerful function used in frameworks like PeopleSoft PeopleCode to execute SQL statements directly against a database. If it is implemented incorrectly by embedding raw user input directly into the query string, attackers can bypass security controls and manipulate your database. The Anatomy of a Vulnerable Implementation
The primary cause of SQL injection in SqlExec is string concatenation. When user input is combined directly with static SQL text, the database treats the entire input string as executable command code rather than passive data. ❌ Dangerous Pattern (Concatenation)
In this example, an attacker can input O’Reilly or add harmful commands like ’ OR ‘1’=‘1 to bypass authentication or extract sensitive records. peoplecode
/VULNERABLE: Direct string concatenation / &UserInput = GetField(FIELD.USER_INPUT).Value; &SQL = “SELECT DESCR FROM PS_MY_TABLE WHERE EMPLID = ‘” | &UserInput | “’”; SQLExec(&SQL, &Result); Use code with caution. Best Practices to Secure SqlExec
Securing SqlExec requires enforcing a strict separation between the SQL instruction structure and the user-supplied data. 1. Use Native Bind Variables (The Gold Standard)
PeopleCode supports native bind variables using the %Bind syntax or parameter markers (:1, :2, etc.). When you pass variables as parameters at the end of the SqlExec function, the database pre-compiles the query and handles the input strictly as literal values. peoplecode
/ SECURE: Using numbered bind variables */ &UserInput = GetField(FIELD.USER_INPUT).Value; SQLExec(“SELECT DESCR FROM PS_MY_TABLE WHERE EMPLID = :1”, &UserInput, &Result); Use code with caution. 2. Implement Strict Input Validation
Never allow arbitrary data into your business logic. Validate data format, length, and type before it ever interacts with a query line. SQL Injection Prevention – OWASP Cheat Sheet Series
Leave a Reply