1 / 9

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

Accessing Your MySQL Database from the Web with PHP (Ch 11). 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:

jmabie
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related