Comp 353/453: Database Programming, LT 410, 4:15 Tuesdays
Week 8
JDBC programming, E&N §13.3
JDBC and SQLJ are two popular Java interfaces to SQL. Note that SQLJ
requires that your sql statements be compiled into your program; this
is a major problem. JDBC allows dynamically created strings to be used
for queries. Still, the strings are interpreted, leaving room for problems. The worst such problem is "SQL Injection".
Documentation is found in under "java.sql".
Note that JDBC would only be applicable if you were building your
application with java. Perhaps most web interfaces are browser-based.
I need to make sure that my CLASSPATH contains mysql_jdbc.jar.
We'll look at two examples. The first is employees1.java,
which shows the process of creating a Connection object. This object
knows that we are creating a connection to MySQL (some of the
commented-out parts relate to Oracle). The book's way of creating a
connection is slightly different, using
Class.forName("oracle.jdbc.driver.OracleDriver");
The two amount to the same thing.
The next thing done in employees1 is to create a Statement from the
connection, and then execute the query. The query returns a ResultSet.
Note the getString() method to extract data, and the next() method.
The second program is employees2.java. Here, I use a parameterized query.
Why not build strings manually? See employees3.java.
A problem occurs if there are quotes in the data, particularly if that
data is user-supplied. Suppose the user provides a USERNAME and a
PASSWORD string, and the query does
select * from AUTH_USERS where user=$USERNAME
Suppose the user sends USERNAME= ' or '1'='1
Then the above may be naively formatted into
select * from AUTH_USERS where user='' or '1'='1' (note two extra quotes!)
This is now everyone.
