Preventing SQL Injection

The following functions and methods provide a way for SQL to be submitted to the database; they are, therefore, subject to SQL injection vulnerabilities:

SQLExec function
CreateSQL function
Rowset class Select method
Rowset class SelectNew method
Rowset class Fill method
Rowset class FillAppend method

Look at the following PeopleCode as an example:

rem Retrieve user input from the name field;
&UserInput = GetField(Field.NAME).Value;
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" 
| &UserInput | "'", &Name, &Phone);

The code is meant to enable the user to type in a name and get the person's phone number. In the example, the developer expects that the user will input data such as Smith, in which case the resulting SQL would look like this:

SELECT NAME, PHONE FROM PS_INFO WHERE NAME='Smith'

However, if the user specified "Smith' OR AGE > 55 --", the resulting SQL would look like this:

SELECT NAME, PHONE FROM PS_INFO WHERE NAME='Smith' OR AGE > 55 --'

Note the use of the comment operator (--) to ignore the trailing single quotation mark placed by the developer's code. This would allow a devious user to find everyone older than 55.

Use the following approaches to avoid SQL injection vulnerabilities:

Where possible, avoid using string-building techniques to generate SQL.

Note: String-building techniques cannot always be avoided. String-building does not pose a threat unless unvalidated user input is concatenated to SQL.
Use bind variables where possible rather that string concatenation.
The following example is vulnerable:
```
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" | 
&UserInput | "'", &Name, &Phone);
```
Use the Quote PeopleCode function on the user input before concatenating it to SQL.
This pairs the quotation marks in the user input, effectively negating any SQL injection attack.

The following example is vulnerable:
```
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" | 
&UserInput | "'", &Name, &Phone);
```
This example is not vulnerable:
```
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" | 
Quote(&UserInput) | "'", &Name, &Phone);
```
Specify whether SQL errors appear to the user with the Suppress SQL Error setting in the PSTOOLS section of the application server configuration file. Normally, the SQL in error appears to the user in a number of messages. If you consider this a security issue, add the following line to your application server configuration file:
```
Suppress SQL Error=1
```
When this line is set, SQL errors do not display details; instead, they refer the user to consult the system log. The detail that was in the SQL message is written to the log file.