parameterized queries

Question 1

effective decision support systems require

Answer 1

flexibility in interacting with the user

Question 2

decision makers may not always know

Answer 2

exactly what questions they want/need to ask

Question 3

b/c decision makers may not know what questions they want to ask, it is important to

Answer 3

support “what if?” questions

Question 4

pre-determining a list of potential queries can be

Answer 4

very limiting

Question 5

using parameters within a query allows

Answer 5

the user to decide exactly what data to return

Question 6

with parameters, the database programmer:

Answer 6

sets up the structure/template for the SQL statement, but leaves certain values unspecified

Question 7

parameterized queries greatly improves

Answer 7

the usability of the system

Question 8

parameterized queries are frequently used in (and also used in)

Answer 8

web-based DSS
- also used in desktop applications (.NET -> MS Access)
- also used within actual DBMS

Question 9

within MS Access, parameterized queries can be used by

Answer 9

building userforms and collecting the parameter values as variables from the user

Question 10

parameterized queries can also be called from other applications using a

Answer 10

DB provider/driver
(PHP/ASP.NET/VB.NET/Java)

Question 11

example of calling parameterized queries externally

Answer 11

dim cmd as New OleDbCommand(“SELECT * FROM Customer WHERE LName = ?”)

Question 12

Microsoft Access specific format

Answer 12

parameter order matters
“SELECT * FROM table1 WHERE col1 = ? OR col2 LIKE ?”

Question 13

SQL server specific format

Answer 13

supports named parameters
“SELECT * FROM table1 WHERE col1 = @someCol1 OR col2 LIKE @theColour”

Question 14

Oracle specific format

Answer 14

“SELECT * FROM table1 WHERE col1 = :someCol1 OR col2 LIKE :theColour”

Question 15

advantages of parameterized queries

Answer 15

• query can be compiled once at beginning of process
• data types can be enforced without focus on syntax
• security, can help to protect against SQL injection

Question 16

when query is compiled once at beginning,

Answer 16

parameter values can then be set as needed

Question 17

SQL injection

Answer 17

consists of inserting or injecting SQL code via data that is sent to a DBMS, when (non-parameterized) dynamic SQL is used

Question 18

because a parameterized query is expecting only the values for missing parameters,

Answer 18

it will protect against many SQL injection attacks

Question 19

SQL injection example

Answer 19

; DROP TABLE employee;–