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.
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:
Concepts of the Database System, Interfaces
Database Studio, Testing Prepared Statements
The APPLICATION_USER table contains the users and passwords for a database application that accesses the DEMODB database via the SAP MaxDB JDBC interface.
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.
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
}