Checking User Input in SQL Statements 

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 MaxDB interfaces.

MaxDB Interfaces: Prepared Statements

Interface	Prepared Statements
JDBC	Class PreparedStatement
ODBC	Method SQLPrepare
SQLDBC	Class SQLDBC_PrepareStatement
PHP	maxdb_prepare
Perl	prepare
Python	Method prepare, class SAPDB_Prepared

For more information on the MaxDB interfaces, see Concepts of the Database System, Interfaces.

Example

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

Unsecure Statement

The following user logon implementation is unsecure. It could allow an unauthorized person, by entering an invalid value, to access the database instance without entering a valid password.

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 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

  }