Embedded SQL
Author: Diane Horton
with examples from Ullman and Widom
Problems with using interactive SQL • Standard SQL is not “Turing-complete”.
• E.g., Two profs are “colleagues” if they’ve co-taught a course or share a colleague.
• We can’t write a query to find all colleagues of a given professor because we have no loops or recursion.
• You can’t control the format of its output.
• And most users shouldn’t be writing SQL queries!
• You want to run queries that are based on user input, not have users writing actual queries.
2
SQL + a conventional language
• If we can combine SQL with code in a conventional language, we can solve these problems.
• But we have another problem:
• SQL is based on relations, and conventional languages have no such type.
• It is solved by
• feeding tuples from SQL to the other language one at a time, and
• feeding each attribute value into a particular variable.
3
Call-level interface (CLI)
• Each language has its own set of library functions for this.
• for C, it’s called SQL/CLI
• for Java, it’s called JDBC
• for PHP, it’s called PEAR DB
• We’ll look at JDBC.
4
JDBC
JDBC Example
Do this once in your program:
/* Get ready to execute queries. */
import java.sql.*;
/* A static method of the Class class. It loads the
specified driver */
Class.forName(“org.postgresql.jdbc.Driver”); url =
“jdbc:postgresql://localhost:5432/csc343h-dianeh”; conn =
DriverManager.getConnection(url, “dianeh”, “”); /* Continued … */
6
The arguments to getConnection
URL for the database server, in 3 parts
• jdbc:postgresql
We’ll use this, but it could be, e.g., jdbc:mysql
• localhost:5432
You must use exactly this for the CS Teaching Labs.
• csc343h-dianeh Username dianeh
• Substitute your userid on the CS Teaching Labs. Password “”
• Unrelated to your password on the CS Teaching Labs.
• Literally use the empty string.
7
Do this once per query in your program:
/* Execute a query and iterate through the resulting
tuples. */
PreparedStatement execStat = conn.prepareStatement(
“SELECT netWorth FROM MovieExec”);
ResultSet worths = execStat.executeQuery(); while (worths.next()) {
int worth = worths.getInt(1);
/* If the tuple also had a float and another int
attribute, you’d get them by calling worths.getFloat(2) and worths.getInt(3).
Or you can look up values by attribute name. Example: worths.getInt(“netWorth”)
*/
/* OMITTED: Process this net worth */
}
8
The Java details
• For full details on the Java classes and methods used, see the Java API documentation:
https://docs.oracle.com/javase/8/docs/api/java/sql/p ackage-summary.html
9
Exceptions can occur
• Any of these calls can generate an exception.
• Therefore, they should be inside try/catch blocks.
try {
/* OMITTED: JDBC code */
} catch (SQLException ex) {
/* OMITTED: Handle the exception */
}
• The class SQLException has methods to return the SQLSTATE, etc.
10
Aside: where to run JDBC code
• Our database server (dbsrv1) is configured so that you can only connect to it from that machine.
• So you must run your JDBC code on dbsrv1. • This configuration is for security.
11
What is “preparation”?
• Preparing a statement includes: • parsing the SQL
• compiling • optimizing
• The resulting PreparedStatement can be executed any number of times without having to repeat these steps.
12
If the query isn’t known until run time
• You may need input or computation to determine exactly what the query should be. In that case:
• Hard-code in the parts you know.
• Use the character ? as a placeholder for the values
you don’t know. (Don’t put it in quotes!)
• This is enough to allow a PreparedStatement to be constructed.
• Once you know values for the placeholders, use methods setString, setInt, etc. to fill in those values.
• Note: You can’t use ? in a view.
13
Example (figure 9.22)
PreparedStatement studioStat =
conn.preparedStatement(
“INSERT INTO Studio(name, address)
VALUES(?, ?)”
);
/* OMITTED: Get values for studioName and studioAddr */
studioStat.setString(1, studioName);
studioStat.setString(2, studioAddr);
studioStat.executeUpdate();
14
Why not just build the query in a string?
• We constructed an incomplete preparedStatement and filled in the missing values using method calls.
• Instead, we could just build up the query in an ordinary string at run time, and ask to execute that.
• There are classes and methods that will do this in JDBC.
15
Example where we know the full query
• Here we use a Statement rather than a PreparedStatement, and give it a String to execute.
/* stat cannot be compiled & optimized (yet). */ Statement stat = conn.createStatement();
String query =
“SELECT networth
FROM MovieExec
WHERE execName like ‘%Spielberg%’;”
/* Now compile, optimize, and run the query. */ ResultSet worths = stat.executeQuery(query);
• SQL must do the compile and optimize steps every time we execute.
16
What could possibly go wrong?
17
Example where we build the query string
Suppose we want the user to provide the string to compare to.
You can do this rather than hard-coding Spielberg into the query:
Statement stat = conn.createStatement();
String who = /* get a string from the user */
String query =
“SELECT networth
FROM MovieExec
WHERE execName like ‘%” + who + “%’;” ResultSet worths = stat.executeQuery(query);
18
A gentle user does no harm
If a user enters Milch, the SQL code we execute is this:
SELECT networth
FROM MovieExec
WHERE execName like ‘%Milch%’;
Nothing bad happens.
19
An injection can exploit the vulnerability What could a malicious user enter?
SELECT networth
FROM MovieExec
WHERE execName like ‘%?????????????%’;
20
An injection can exploit the vulnerability
But if a malicious user enters
Milch%’; drop table Contracts; —
the code we execute is this:
SELECT networth
FROM MovieExec
WHERE execName like ‘%Milch%’; DROP TABLE Contracts; –%’;
In other words:
SELECT networth
FROM MovieExec
WHERE execName like ‘%Milch%’;
DROP TABLE Contracts; –%’;
Ouch! 21
Reference: https://xkcd.com/327/
Always use a PreparedStatement • This was an example of an injection.
• The simple approach of giving a String to a Statement is vulnerable to injections.
• Moral of the story:
Always use a PreparedStatement instead.
23
Queries vs updates in JDBC
• The previous examples used executeQuery.
• This method is only for pure queries.
• For SQL statements that change the database (insert, delete or modify tuples, or change the schema), use the analogous method executeUpdate.
24