SQL: Programming
Introduction to Databases
CompSci 316 Fall 2014
2
Announcements (Tue., Oct. 7)
• Homework #2 due today midnight
• Sample solution to be posted by tomorrow evening
• Midterm in class this Thursday
• Open-book, open-notes
• Same format as the sample midterm (posted on Sakai)
• Q&A session on sample midterm conducted by Ben
• Wednesday 6-8pm in Link
• Project milestone #1 due next Thursday
3
Motivation
• Pros and cons of SQL
• Very high-level, possible to optimize
• Not intended for general-purpose computation
• Solutions
• Augment SQL with constructs from general-purpose
programming languages
• E.g.: SQL/PSM
• Use SQL together with general-purpose programming
languages
• E.g.: Python DB API, JDBC, embedded SQL
• Extend general-purpose programming languages with
SQL-like constructs
• E.g.: LINQ (Language Integrated Query for .NET)
4
An “impedance mismatch”
• SQL operates on a set of records at a time
• Typical low-level general-purpose programming
languages operate on one record at a time
Solution: cursor
• Open (a result table): position the cursor before the first
row
• Get next: move the cursor to the next row and return
that row; raise a flag if there is no such row
• Close: clean up and release DBMS resources
Found in virtually every database language/API
• With slightly different syntaxes
Some support more positioning and movement options,
modification at the current position, etc.
5
Augmenting SQL: SQL/PSM
• PSM = Persistent Stored Modules
• proc_name param_decls
local_decls
proc_body
• func_name param_decls
return_type
local_decls
func_body
• proc_name params
• Inside procedure body:
variable func_name params
6
SQL/PSM example
Enforce ; return # rows modified.
! " " #
$%!
A cursor to range over all users:
&! & !
!
Set a flag upon “not found” exception:
! #
'
! (
… (see next slide) …
! " "
7
SQL/PSM example continued
Fetch the first result row:
&! & !
' &! & ! $%!
Loop over all result rows:
)' ! *+ (
$%! + '
Enforce newMaxPop:
!
)' &! & !
Update count:
! " " ! " " , (
Fetch the next result row:
' &! & ! $%!
)'
&! & !
8
Other SQL/PSM features
• Assignment using scalar query results
•
• Other loop constructs
• , ,
• Flow control
•
• Exceptions
• ,
…
• For more PostgreSQL-specific information, look for
“PL/pgSQL” in PostgreSQL documentation
• Link available from course website (under Help:
PostgreSQL Tips)
9
Interfacing SQL with another language
• API approach
• SQL commands are sent to the DBMS at runtime
• Examples: Python DB API, JDBC, ODBC (C/C++/VB)
• These API’s are all based on the SQL/CLI (Call-Level
Interface) standard
• Embedded SQL approach
• SQL commands are embedded in application code
• A precompiler checks these commands at compile-time
and converts them into DBMS-specific API calls
• Examples: embedded SQL for C/C++, SQLJ (for Java)
10
Example API: Python !-. /0
%1 !-. /0
. !-. /02. . "3 1 43 !4
.& . 2.& !
5 list all drinkers:
You can iterate over .&
.& 2 .& 4 6 % 7 4 one tuple at a time
8 " % 7 9 "" !! % .& :
Placeholder for
% " % 7 , 4 ;%< ! 4 , "" !!
query parameter
5 print menu for bars whose name contains “a”:
.& 2 .& 4 6 < ! )' 3 = >!49 4> >49
8 3 9 3 9 %. % .& :
% 3 , 4 ! < ! 4 , 3 ? Tuple of parameter values,
, 4 @A:9208B428 1 %.
one for each >!
.& 2.; ! (note that the trailing “9” is needed when
the tuple contains only one value)
. 2.; !
11
More !-. /0 examples
5 “commit” each change immediately—need to set this option just once at the start of the session
. 2! C! !!% & . 11% &
5 222
3 C% & 4 $ 3 & " : 4 2! %
3 C% & 4 $ 3 & " : 4 2! %
%. 8; C% & 4 $ %. : 4
-:
.& 2 .& 444
< !
%. >!
)' 3 >! 3 >!4449 %. 9 3 9 3
%8 .& 2 . & D (:
% 4AB ! & " ": . . 3 E3 F4?
28 1 .& 2 . &
# of tuples modified
. . % ! :
% Exceptions can be thrown
(e.g., if positive-price constraint is violated)
12
Prepared statements: motivation
$%; & :
5 Input bar, beer, price…
.& 2 .& 444
< !
%. >!
)' 3 >! 3 >!4449 %. 9 3 9 3
5 Check result...
• Every time we send an SQL string to the DBMS, it
must perform parsing, semantic analysis,
optimization, compilation, and finally execution
• A typical application issues many queries with a
small number of patterns (with different parameter
values)
• Can we reduce this overhead?
13
Prepared statements: example
See
on your VM for a complete code example
.& 2 .& 444 5 Prepare once (in SQL).
& " C %. 5 Name the prepared plan,
< !
%. @( 5 and note the @(, @0, … notation for
)' 3 @0 3 @G444 5 parameter placeholders.
$%; & :
5 Input bar, beer, price…
.& 2 .& 4 H & " C %. >!9 >!9 >! 49? 5 Execute many times.
%. 9 3 9 3
# Note the switch back to >! for parameter placeholders.
5 Check result...
• The DBMS performs parsing, semantic analysis,
optimization, and compilation only once, when it
“prepares” the statement
• At execution time, the DBMS only needs to check
parameter types and validate the compiled plan
• Most other API’s have better support for prepared
statements than !-. /0
• E.g., they would provide a .& 2 method
14
“Exploits of a mom”
$ :EE 7."2. 1EG0JE
• The school probably had something like:
.& 2 .& I 6 &" ! I , ?
I)' 1 4I , , I4 I
where 1 is a string input by user
• Called an SQL injection attack
15
Guarding against SQL injection
• Escape certain characters in a user input string, to
ensure that it remains a single string
• E.g., 4, which would terminate a string in SQL, must be
replaced by 44 (two single quotes in a row) within the
input string
• Luckily, most API’s provide ways to “sanitize” input
automatically (if you use them properly)
• E.g., pass parameter values in !-. /0 through >!’s
16
Augmenting SQL vs. API
• Pros of augmenting SQL:
• More processing features for DBMS
• More application logic can be pushed
closer to data
• Less data “shipping,” more optimization
opportunities ⇒ more efficient
• Less code ⇒ easier to maintain multiple
applications
• Cons of augmenting SQL:
• SQL is already too big—at some
point one must recognize that
SQL/DBMS are not for everything!
• General-purpose programming
constructs complicate optimization
and make it impossible to guarantee
safety
17
A brief look at other approaches
• “Embed” SQL in general-purpose programming
languages
• E.g.: embedded SQL
• Extend general-purpose programming languages
with SQL-like constructs
• E.g.: LINQ (Language Integrated Query for .NET)
18
Embedded SQL
• Embed SQL inside code written in a general-
purpose language
• Special keywords mark code sections containing SQL or
variables holding data to be passed to/from SQL
• A “pre-compiler” parses the program and
automatically convert the special sections to code
with appropriate API calls
• Pros: more compile-time checking, and potentially more
optimization opportunities
• Cons: DBMS-specific:
• Different pre-compilers for different DBMS vendors
• Program executable not portable across DBMS’s
• Difficult for a program to talk to DBMS’s from different vendors
19
Embedded SQL example (in C)
H K Declare variables to be “shared”
% $%! %" 8; $%!
H K between the application and DBMS
H K 13
&%"9 !
)' &%" &%" 13 )' /%" 4 3.4
H K 13
H K )' L 3 7
Specify a handler for
exception
$%; ( A
H K ' 13 : $%! %"9 : $%!
% 8 I&%" >": .& %! >8? I9 $%! %"9 $%!
% 8 I &; % -: I
!. 8 I>8I9 M $%!
H K ! : $%!
)' 13
B
H K 13
20
Adding SQL to a language
• Example: LINQ (Language Integrated Query) for
Microsoft .NET languages (e.g., C#)
% ! 1 L ;& N
< !&; ! 8 1 . % ! 1 ;; . %
; ! 1 L ;& 6 0
$ .2 1 - *
! ; . A.2 1 -9 .2 $ -B
8 .$ < !&; % !&; ! A
! ; 2) % % !&;
B
• Automatic data mapping and query translation
• But syntax may vary for different host languages