<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <!-- demo inserting a new department, committing that change, and setting Oracle bind variables from PHP by: Peter Johnson adapted by: Sharon Tuttle last modified: 2016-03-09 --> <head> <title> committing and binding </title> <meta charset="utf-8" /> <link href="http://users.humboldt.edu/smtuttle/styles/normalize.css" type="text/css" rel="stylesheet" /> <link href="lect08-1.css" type="text/css" rel="stylesheet" /> </head> <body> <h1> Committing and Binding </h1> <?php // when first called, show an enter-dept-info form if (! array_key_exists("username", $_POST) ) { ?> <form method="post" action="<?= htmlentities($_SERVER['PHP_SELF'], ENT_QUOTES) ?>" > <fieldset> <fieldset> <legend> Enter Oracle username/password: </legend> <label for="user_name"> Username: </label> <input type="text" name="username" id="user_name" /> <label for="pass_word"> Password: </label> <input type="password" name="password" id="pass_word" /> </fieldset> <fieldset> <legend> Enter new department's info </legend> <label for="deptnum"> Dept Number: </label> <input type="text" name="dept_num" id="deptnum" /> <label for="deptname"> Dept Name: </label> <input type="text" name="dept_name" id="deptname" /> <label for="deptloc"> Dept Location: </label> <input type="text" name="dept_loc" id="deptloc" /> </fieldset> <div class="submit"> <input type="submit" value="Add to Dept" /> </div> </fieldset> </form> <?php } // otherwise, try to log in and insert new department else { // I am being paranoid and stripping tags // from the username $username = strip_tags($_POST['username']); // I am ONLY logging in using the password; // so I am (gulp!) just using it; $password = $_POST['password']; $db_conn_str = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = cedar.humboldt.edu) (PORT = 1521)) (CONNECT_DATA = (SID = STUDENT)))"; // try to log in using this string $conn = oci_connect($username, $password, $db_conn_str); // exit if you couldn't log in if (! $conn) { ?> <p> Could not log into Oracle, sorry </p> <?php require_once("328footer.txt"); exit; } // I logged in if I reached here! yay! // I can just trustingly build my insert from // the user input, right? NOOOOOOO!!!!! // GOT to protect against SQL injection, // cross-site scripting // we'll do TWO things to help protect ourselves: // strip any tags (dept info should NOT include) // AND use bind variables $insert_string = 'insert into dept values (:new_dept_num, :new_dept_name, :new_dept_loc)'; $insert_stmt = oci_parse($conn, $insert_string); $new_dept_num = strip_tags($_POST['dept_num']); $new_dept_name = strip_tags($_POST['dept_name']); $new_dept_loc = strip_tags($_POST['dept_loc']); // in PHP using OCI, // oci_bind_by_name lets you bind the Oracle bind // variables to values oci_bind_by_name($insert_stmt, ":new_dept_num", $new_dept_num); oci_bind_by_name($insert_stmt, ":new_dept_name", $new_dept_name); oci_bind_by_name($insert_stmt, ":new_dept_loc", $new_dept_loc); // now execute! non-selects, when executed, // return number of rows affected, // (or 0 for statements that don't affect rows) $num_inserted = oci_execute($insert_stmt, OCI_DEFAULT); if ($num_inserted == 0) { ?> <p> SORRY, no row inserted! </p> <?php } else { ?> <p> 1 row inserted! </p> <?php oci_commit($conn); } oci_free_statement($insert_stmt); oci_close($conn); } require_once("328footer-better.html"); ?> </body> </html>