Accessing Your MySQL Database from the Web with PHP (Ch 11)

1. Accessing Your MySQL Database from the Web with PHP (Ch 11)

2. Using Prepared Statements • Prepared statements – recommended for: • Speeding up execution when the same query is performed a large number of times, with different data. • Protecting against SQL injection attacks. • Basic concept: • A template of the query to execute is sent to MySQL • Data (parameters) for the query is sent separately • The query template can be used multiple times with different sets/lots of data

3. Using Prepared Statements • Prepared statementsin mysqli library • The query string is not built on the fly, based on user-provided input! • Instead, the query is parameterized • When setting up the query, mark each piece of data that will be fed later into the query with ? – called placeholders $query = "insert into books values(?, ?, ?, ?)"; • Create a statement resource suitableforpreparingandexecutinganSQLquery mysqli_stmt_resource mysqli_stmt_init(mysqli_resource $db_link) $stmt = mysqli_stmt_init($link);

4. Using Prepared Statements • Prepared statementsin mysqli library (cont) • Prepare the statement bool mysqli_stmt_prepare ( mysqli_stmt_res $stmt , string $query ) • Bind parameters = tell PHP which variables / values should be substituted for placeholders (?) when the query is executed bool mysqli_stmt_bind_param (mysqli_stmt_res $stmt, string $types , mixed &$var1 [, mixed &$... ]) Theformatstring$typesexplainswhatarethetypes of the parameters passed to the query: s (string), i (integer), d (double) mysqli_stmt_bind_param($stmt, "sssd", $isbn, $author, $title, $price);

5. Using Prepared Statements • Prepared statementsin mysqli library (cont) • Executethe statement bool mysqli_stmt_execute ( mysqli_stmt_res $stmt ) mysqli_stmt_execute($stmt); • ChecknumberofrowsaffectedbyINSERT/UPDATE/DELETEquery int mysqli_stmt_affected_rows (mysqli_stmt_res $stmt) →-1onerror echo mysqli_stmt_affected_rows($stmt) . " book inserted into the database."; • Close statement bool mysqli_stmt_close ( mysqli_stmt_res $stmt )

6. newbook • http://cscdb.nku.edu/csc301/frank/Chapter11/newbook_ps.html • http://www.nku.edu/~frank/csc301/Examples/MySQL/newbook_ps_html.pdf • http://www.nku.edu/~frank/csc301/Examples/MySQL/insert_book_ps_php.pdf

7. Prepared Statements - Advantages • Prepared statements = SQL queries, prewritten and precompiled at the DB server → only require variable inputs to execute. • When sending a query the “normal” way: • client (php script) passes the query as a string to the DB server • DB server converts data back into the proper binary data type • db engine parses the statement and looks for syntax errors • db engine attempts to figure out the most efficient way to execute the statement => a query plan is created • query is executed • When using prepared statements: • data are sent to the DB server in a native binary form=>no data conversion, more efficient data transfer • query is parsed only once, and the execution plan is cached (depends on what db server / version is used) • security

8. Prepared Statements - Limitations • Executing a prepared statement - steps: → ‘Pre-query’ is sent to the server for processing → The parsed syntax is checked for errors → An OK message is sent back to the client → Variables are then sent and processed → Results are sent back. • For queries executed only once => longer to execute • Repetitive, complicated queries => faster

9. Prepared Statements - security • Securitywithprepared statements • SQLinjection=anattackmethodconsistinginpassing/injectingaSQLqueryasaninput, possibly via web pages,in hopes of gaining unauthorized access to a database /application. How: • Ex: a login web page that collects ausername and apassword, creates anad-hocSQL query to the db to check if the user is valid. SQL Injection=send crafted user name and/or password inputthat will alter the SQL query and thus grant us something else. • Solution:codetofilterandsanitizeinput,usepreparedstatements