Background documentationChecking User Input in SQL Statements Locate this document in the navigation structure

 

To prevent users from entering invalid values in SQL statements and thereby causing unwanted changes to the data records or to the behavior of the database application (SQL injection), we recommend the use of prepared statements.

The following table shows which prepared statements can be used with which SAP MaxDB interfaces.

SAP MaxDB Interfaces: Prepared Statement Support

Interface

Implementation

JDBC

PreparedStatement class

ODBC

SQLPrepare method

SQLDBC

SQLDBC_PrepareStatement class

PHP

maxdb_prepare

Perl

prepare

Python

Method prepare (class SapDB_Session), Class SapDB_Prepared (module sdb.sql)

More information:

Example

The APPLICATION_USER table contains the users and passwords for a database application that accesses the DEMODB database via the SAP MaxDB JDBC interface.

Insecure Statement

The following user logon implementation is insecure. By entering an invalid value, an unauthorized person could gain access to the database.

Statement s = connection.createStatement();

ResultSet rs = s.executeQuery("SELECT * FROM APPLICATION_USERS WHERE username = '" + username + "' and password = '" + password + "'");

if(rs.next()) {

    // ... continue with successful logon

    } else {

// ... unsuccessful logon

}

If a person knows a valid user name, he or she can log on to the database without entering a valid password. The value abcdefg′ or 1=1, for example, could be entered for password.

The password would then look like this: password='abcdefg' or 1=1'

Because 1=1 is always true, the database system always evaluates the whole expression for the password as true, regardless of whether abcdefg is a valid password or not.

Improved Statement

The following user logon implementation makes use of prepared statements and protects against SQL injection because special characters like ', for example, can no longer be entered as a password.

PreparedStatement ps = connection.prepare("SELECT * FROM APPLICATION_USERS WHERE username=? and password=?");

ps.setString(1, username);

ps.setString(2, password);

ResultSet rs = ps.executeQuery();

if(rs.next()) {

    // ... continue with successful logon

} else {

// ... unsuccessful logon

}