JDBC_SQLINJ_AUT Flashcards
What is JDBC short for?
JDBC = Java DataBase Connectivity
Functions of JDBC?
JDBC is Java’s call-level interface to SQL
DBMS’s.
– A library with operations that give full access
to relational databases, including:
• Creating, dropping or altering tables, views, etc.
• Modifying data in tables
• Querying tables for information
Explain JDBC
JDBC Objects
• JDBC is a library that provides a set of classes
and methods for the user:
– DriverManager
• Handles connections to different DBMS. Implementation
specific.
– Connection
• Represents a connection to a specific database.
– Statement, PreparedStatement
• Represents an SQL statement or query.
– ResultSet
• Manages the result of an SQL query.
Explain statement object.
2 fundamental methods of statement objects?
Exceptions in JDBC?
How to Execute Queries.
Which commando holds the results of an SQL query?
Important note on ResultsSet.
ResultSet is not a result set!
• Remember a ResultSet is more like a
cursor than an actual set – it is an
interface to the rows in the actual result
set.
• A Statement object can have one result
at a time. If the same Statement is used
again for a new query, any previous
ResultSet for that Statement will no
longer work!
If we need information from more than one table,
there are two different programming patterns for
doing so:
Two approaches
• If we need information from more than one table,
there are two different programming patterns for
doing so:
– Joining tables in SQL
• Join all the tables that we want the information from in a
single query (like we would in SQL), get one large result set
back, and use a ResultSet to iterate through this data.
– Use nested queries in Java
• Do a simple query on a single table, iterate through the
result, and for each resulting row issue a new query to the
database (like in the example on the previous page, but
without the error).
Example of Joining in SQL.
Example using nested queries in JAVA.
Comparison of Joining in SQL and Nested Queries in JAVA
Comparison
• Joining in SQL
– Requires only a single query.
– Everything done in the DBMS, which is good at
optimising.
• Nested queries
– Many queries to send to the DBMS
• communications/network overhead
• compile and optimise many similar queries
– Logic done in Java, which means optimisations must
be done by hand.
– Limits what can be done by the DBMS optimiser.
What does Push complexity to DBMS means?
Explain Dynamically Generated Queries.
What are a Naive Approach to dynamically generated queries?
Explain SQL injections.
What is Prepared Statement used for?
Why are Prepared Statement Superios? (3 reasons)
- Security
- Performance
3 Easier to read/write.
Explain
PreparedStatement is superior:
Reason 1 – Security
Explain PreparedStatement is superior:
Reason 2 – Performance.
Explain
PreparedStatement is superior:
Reason 3 – easier to read/write
How to prevent SQL injection?
Summary JDBC
Explain Database authorization.
Explain difference between a database vs file system.
Database vs file system
• A (UNIX) file system has:
– Privileges on files.
– Three different privileges: read, write, execute
– Three levels of access: owner, group, all
• A database has:
– Privileges on schema elements (tables, views,
triggers, etc.)
– Nine different privileges.
– Any number of levels of access – each user can be
given different access.
Explain privileges on relations
Privileges on relations
• SELECT (attributes) ON table
– Allows the user to select data from the specified table.
– Can be parametrized on attributes, meaning the user
may only see certain attributes of the table.
• INSERT (attributes) ON table
– Allows the user to insert tuples into the table.
– Can be parametrized on attributes, meaning the user
may only supply values for certain attributes of the
table. Other attributes are then set to NULL.
DELETE ON table
– Allows the user to delete tuples from the
table.
– Cannot be parametrized on attributes.
• UPDATE (attributes) ON table
– Allows the user to update data in the table.
– Parametrizing means the user may only
update values of certain attributes.
REFERENCES (attributes) ON table
– Allows the user to create a foreign reference to
(attributes of) that table.
• TRIGGER ON table
– Allows the user to create triggers for events on that
table.
• EXECUTE ON procedure
– Allows the user to execute the procedure or function,
and use it in declarations.
• USAGE, UNDER, TRUNCATE, CREATE, ALL,
…
Explain Execute and Trigger.
EXECUTE and TRIGGER
• When writing a trigger, the body may perform
selections and modifications.
– The user who writes the trigger must have all the
necessary privileges to perform those operations,
plus the TRIGGER privilege.
– The user that sets off the trigger needs only the
privilege to perform the triggering event (e.g. an
insertion). Everything that happens in the trigger is
considered done by its creator.
• The same thing goes for procedures and
functions – it is the privileges of the creator that
decides what operations may be performed, and
the user needs only EXECUTE.
Explain Granting Privileges.
Granting privileges
• You have all possible privileges on
elements that you have created.
• You may grant privileges to other users on
those elements.
– A user is referred to by an authorization ID,
which is typically a user name.
– There is a special authorization ID, public
– Granting a privilege to public makes it
available to all users.
How to write a GRANT statement.
Explain GRANT option.
Explain Revoking Privileges.
Explain Grant diagrams.
Grant diagrams
• Nodes = user + privilege + option
– Option is either owner, WITH GRANT
OPTION, or neither.
– UPDATE ON T, UPDATE(a) ON T,
UPDATE(b) ON T and UPDATE ON T WITH
GRANT OPTION all live in different nodes.
• Edge X → Y means that node X was used
to grant Y.
Explain Manipulating Edges.
Manipulating edges
• If A grants P to B, we draw an edge from AP* (or
AP**) to BP(* if with grant option).
• Revoking a privilege means deleting the edge
corresponding to the privilege.
• Fundamental rule: User U has privilege P as
long as there is a path from XP** to either UP,
UP* or UP**, where X is the owner of P.
– Note that X could be U, in which case the path is 0
steps.
Summarize main points concerning Authorization.
Summary Authorization
• Privileges in SQL
– SELECT, INSERT, DELETE, UPDATE,
REFERENCE, TRIGGER, EXECUTE …
• Granting and revoking privileges
– Authentication IDs, public
– WITH GRANT OPTION
• Grant diagrams
