home—lects—exams—hws
D2L—breeze (snow day)

mysqli
MySQL
chapter 12

From PHP Visual Quickstart Guide by Larry Ullman.
Originally base on notes by Jack Davis (jcdavis@radford.edu)

This chapter will focus on accessing MySql database tables from PHP programs. A database is a collection of tables (rows & columns) that store information. Databases are created, updated, and read using SQL (Structured Query Language). There are surprisingly few commands in SQL. SQL was designed to be written a lot like the English language, which makes it very user friendly; but it does take some thought to create more elaborate SQL statements with only the handful of available terms.

The process is quite simple: PHP is used to send SQL statements to the database application, where they are executed. The result of the execution - the creation of a table, the insertion of a record, the retrieval of some records, or even an error - is then returned by the database to the PHP script.

Setting up a MySQL database on your RU account:
- Go to php.radford.edu > MySQL hosting page.
- Follow the instructions for 'Request/create a MySQL database'.
- Once you have set up your MySQL account, you can write php programs that save/retrieve info from your database.
- If you want to interact with your database via MySQL Admin Tool, you can follow links to php.radford.edu > MySQL hosting page > RU Mysql Admin Tool.
If you want to connect to the department Oracle server you used in itec340, instead of a MySQL database, here is an example using Oracle: db-oracle-connect.php.
PHP's mysqli_query function is the most-used command. It sends an SQL command to MySQL, and returns the result from the DB:
$result = mysqli_query(connection, SQL statement);
However, before we do this, we need to get a connection to the database: create the connection (with username and password), and then select which schema you want to use (in this class, you'll use the one personal schema that RU gives you).

RU's connection to a MySQL database —

$conn = mysqli_connect(hostname, username, password, schemaToUse );
// @param hostname  For php.radford.edu, use 'localhost'
// @param username  the database account, which is the same as your RU userid
// @param password  the *database* password.
// @param schemaToUse your RU userid (again!) -- it's a DB account *and* a DB-schema.

Eventually, when you are done working with the database, be sure to close the connection!
mysqli_close(connection);
Although the database and/or php will eventually reclaim old, unused database connections, it's always better to do so yourself.
(Inadvertent denial-of-resource attack: students create connections w/o closing them; after 10 students each running their program 10 times, the DB server gets clogged up, and new connections start taking a long time!)

How to handle a failed-connection:

If the database connection fails, you want to throw an exception:
throw new Exception("Error: surroundingFunction: database connection failed." ).

A common expediency is to crash — by calling die. This is lousy practice — exceptions are explicitly created for this purpose instead! (and it's not any more difficult to do). Some notes:

If you really need to, you can do some contortions to catch a die.)

Beware being sneaky, using ||: A common snippet seen in web pages is:

$connect = mysqli_connect(...) || die('connect failed.');
echo "The connection ", ($connect ? "" : "NOT "), "established.\n";

// If I use '... = connect || die' like this, then the following fails(!):
$info = mysqli_query( $connect, "SELECT * FROM Students" );

Note that the green and red text don't mingle: The boolean operator || always returns a boolean (not merely boolean-ish), which not the same as whatever the result of mysqli_connect was. So when we try mysqli_querythe first argument is true, and not a connection-resource.

Beware being sneaky, using or: The “or” operator is just like the || operator, except not:
- || works with true-ish values, but always returns a real boolean, while or returns the true-ish value that triggered the short-circuit (or, the last value).
- || has medium-high precedence, while or has very-low precedence, lower even that =.
This means that writing $connect = mysqli_connect(...) or die('connect failed.'); is the same as writing ($connect = mysqli_connect(...) ) or die('connect failed.'); — which does what we want, but is relying on some subtle corner-cases that other programmers may not expect/understand.
(Note that you'll see this construct because an older, deprecated function mysql_query had a convenient feature that the connection was optional, and defaulted to the most-recently-opened-connection. Nowadays, the preferred solution is to use a connection-object, and call it's method query.)

Using odbc_* as opposed to mysqli_*:
1. Alas, some important function have no generic odbc counterpart: e.g. mysqli_real_escape_string.
2. There are some minor differences: e.g. there is no odbc_select_db because that functionality is subsumed by odbc_connect's more general first argument (which is a connection-string including many details).
3. odbc_connect is not installed on php.radford.edu (I'm not sure why — isn't it a provided, standard php function?)
4. Example To connect to an oracle database (instead of mysql), the function names are prefixed with “oci”.
  See: db-oracle-connect.php
  (be sure to close the connection after making it)
Security note: Your php code contains your database password. Be extra sure your .php file is not public-readable (that is, readable by any other user logged in to rucs)!
Common strategy: Put your 'connect' statements into a separate, included file. (a) This helps because you'll be re-using this code many times; (b) even if you show_source of the main file, your password is in the other file.

One kludgy way of doing this is to have the include-file store the connection it creates in a special variable — perhaps “theConnection” — and then all your files using the database will all have to remember ot use that one variable (and not to use that variable themselves).

Better, I suggest having that file define a function named something like DB_connect_as_ibarland(), and it returns the database connection. That lets each file use whatever variable it wants to store the result (though yes, each file still needs to not trample on the name of that function.)

It's also possible to simply have the external file define a couple variables (like $dbUserName = … and $dbUserPasswd = …), and keep that file non-public.
Common SQL Commands
- SELECT - retrieves records from a table
- INSERT - adds a record to the table
- UPDATE - updates records in a table
- DELETE - deletes individual records from a table
- CREATE - creates a table
- DROP - deletes a table

INSERT -


$var = 'Amy Johnson';
$qu = "INSERT INTO $tablename (numItems,product,custName) VALUES (17,'abc','$var')";
$allRows = mysqli_query($connect, $qu);

SELECT -

$qu = "SELECT * FROM $tablename WHERE (product = 'abc')";

$allRows = mysqli_query($connect, $qu);

After execution of this query, $allRows will presumably be a table — “bunch” of rows (PHP/Mysql calls it a “resource”). You'll then want to process each row, one-by-one:

while ($oneRow = mysqli_fetch_array($allRows)) {  # Note use of `=` for assignment *and* return value
  echo $oneRow['custName'], " bought ", $oneRow['product'];
  // We could also use $oneRow[0], $oneRow[1], $oneRow[2] but it's preferred to use column-name when possible.
}

See also: mysqli_fetch_all: it gets the entire table into a single array. What are the efficiency implications of this? (And, do we care?)

You might want to check for an error — something like the database complaining that it wasn't a valid SQL statement, or that the database connection had been lost (after being previously created).

$allRows = mysqli_query($connect, $qry);         // A table object (possibly w/ 0 rows); NULL on query-failure.
if (!$allRows)  echo "query failed -- lost connection?";

Note that getting back a table with 0 rows is not an error!

If you know your query should always return 1 row, so you won't have a loop. Other times, you just want to know whether the query returned 0 or 1 rows (e.g., you queried something like studentID='1234' and libraryFine = 0). In that case, you might have:

$allRows = mysqli_query($connect, $qry);         // A table object (possibly w/ 0 rows); NULL on query-failure.
if (!$allRows)  echo "query failed -- lost connection?";

$topRow = mysqli_fetch_array($allRows);

if (!$topRow)  {
    echo "table had 0 rows"
    }
else {
    echo "table had (at least) 1 row".
    }

Even if your query should return 0 or 1 rows, because of SQL injection (below) be wary about assuming "there was at least 1 row" with "there was exactly 1 row"; you might want to check:

$allRows = mysqli_query($connect, $qry);         // A table object (possibly w/ 0 rows); NULL on query-failure.
if (!$allRows)  echo "query failed -- lost connection?";

$numRows = mysqli_num_rows($allRows);
if ($numRows == 0) {
    echo "table had 0 rows"
    }
else if ($numRows == 1) {
    echo "table had exactly 1 row".
    $topRow = mysqli_fetch_array($allRows);
    }
else {
    echo <<<END_OF_MSG
    Table had more than 1 row.
    If I think my query shouldn't every yield > 1 row,
      then I might just have built my query incorrectly.

    OR, if I incorporated user-input when building my query,
    then my code may have just succumbed to a SQL-injection attack! 
    Read below, about using  on user-input,
    before splicing it into a SQL query.
    END_OF_MSG;
    }

If you didn't loop through every row of the table yourself, then you should tell the server it doesn't need to keep holding the other results: mysqli_free_result( $allRows );. (Or, if you've been quick and the DB is still processing the (end results of) the query, this will even get the DB server to cancel its work.) Even better, if you know in advance that you only want 1 (or 7, or 29) rows, some SQL variants allow you to specify that in the SELECT statement via a LIMIT or ROWNUM < n clause.

DELETE -
$qu = "DELETE FROM $tablename WHERE (field1 = 'abc')";
To see if a DELETE query worked, you can use the mysqli_affected_rows($connect)
this function returns the number of rows affected by an INSERT, DELETE, or UPDATE
UPDATE -
$qu = "UPDATE $tablename SET $field_name1 = 'abc' , $field_name2 = '$newval' WHERE ($field_name1 = '$var')"; $allRows = mysqli_query($connect, $qu);
Remember that usually, when using SQL's UPDATE and DELETE, we'll provide a WHERE clause involving the table's primary key, so that the update/delete targets just one row. (You can certainly do mass updates/deletes, but they're not as common.)
Example: mysqli-example.php.
Also, you can look at object-oriented version: instead of someMethod($someObj, $otherArgs …) you just ¹ write $someObj->someMethod($otherArgs …).

Constraints in mysql

Warning: In order to get a Foreign Key constraint, you must create both tables involved with the Storage Engine option as "InnoDB". (Sadly, on php.radford.edu, this isn't installed, and you have to settle for the MyISAM engine which doesn't allow Foreign Key constraints.) Then, after the table is created, you can add the FK constraint one of two ways:
Via a raw SQL command: In yourTable > SQL, type in the exact SQL command, such as:
-- To create a FK 
alter table Goo
add constraint Goo_ij_FK 
Foreign key Goo(i,j)
references Foo(m,n)

-- To delete:
alter table Goo
drop foreign key Goo_ij_FK
  -- it seems odd to me, to say 'drop foreign key ...' rather than 'drop constraint ...'.
Via the GUI: In yourTable > Structure > Relation View, in the column to the right of “internal”, you can set the foreign key. You can't use the GUI approach if your key involves a composite key.

Warning: MySQL ignores check constraints — it lets you specify them, but ignores them w/o letting you know(!).

Rumor: I saw one reasonable-sounding post that claimed that you also have to build an index on both the source and destination key-columns.

tip: If googling problems, stackoverflow tends to have better-curated answers than most sites. (e.g. this one)

Q: What is the problem with the following?:
1. $theItem = "coffee"; $theQuery = "SELECT * FROM Inventory WHERE (product = $theItem)"; $results = mysqli_query($connect, $theQuery );
  A1: in the string “SELECT * FROM Inventory WHERE (product = coffee)”, there are no SQL-quotes around the value “coffee”, so that the contents of $theItem is interpreted as a SQL expression (not a string constant): “SELECT * FROM Inventory WHERE (product = '$theItem')”.
  This is a very common sort of error, and it can be detected/debugged by printing $theQuery, and running that query on the database by hand to check whether it really is legal SQL.
2. What if we change the first line to $theItem = htmlspecialchars($_POST['itemSelection']);? (Presuming we came from a form with a text-entry named itemSelection).
  A2: If somebody had entered the four-character string “M&Ms” we would not want want to look in our database for something including the characters “amp”. (Our database should include the correct name of the product; we'll turn “M&Ms” into “M&Ms” only when we display that on a web page — but not in emails, internal reports, etc..)
3. Okay, so we shouldn't use htmlspecialchars since we're not converting the raw-data to HTML-presentation-data.
  So it seems our first line should be $theItem = $_POST['itemSelection']; to use info from a form, right?
  A3: What if the user had entered a name that contains a single-quote? See below, re SQL Injection!

SQL Injection Attacks

Here is some typical code which validates a user, by looking up their name/password in a table we have previously created:

$user = $_POST['username'];
$passwd = $_POST['password'];
$qry = "SELECT * 
        FROM MyPasswords
        WHERE name='$user' and pass='$passwd'";  // DANGER!  SQL injection possible.
$result = mysqli_query($connect,$qry);           // A table object (possibly w/ 0 rows); NULL on query-failure.
if (mysqli_fetch_assoc($result)) {    // Safer: `if (mysqli_num_rows($result) == 1) ...`
    // login successful -- we found that name/password in our db!
    }

If the user had typed in fred and xyz123, then sure enough the database query would be (as we expect)

$qry = "SELECT * 
        FROM MyPasswords
        WHERE name='fred' and pass='xyz123'";

BUT: What if the user had typed in a single-quote in their input? Suppose they typed O'Toole — why will the query fail?
WORSE: Can the user type in something so that the SELECT statment always returns a result?
Alas, yes! Something like: ' or 'hi' = 'hi leads to a query that always turns true.

$qry = "SELECT * 
        FROM MyPasswords
        WHERE name='fred' and pass='' or 'hi' = 'hi'";²

Warning: Always call mysqli_real_escape_string on any user-provided string that is getting integrated into a database query.

Now you may feel that if you've already done other validation on the data and know it doesn't contain any single-quotes or other characters SQL might mis-interpret, then you're safe. That's true. However, you should still call mysqli_real_escape_string! The primary reason is changing specs: When Peter O'Toole calls your boss to complain that your web form doesn't allow apostrophes, and your boss orders some schmo new programmer to change the validation-code for the form to allow apostrophes, it's easy to to miss that you have suddenly opened your site to SQL injection attacks!

(Another reason to call mysqli_real_escape_string even if it feels like it's not necessary: Anybody reviewing your code who sees a lack of calling that function would otherwise spend minutes/hours scrutinizing the code to check for possible attacks, perhaps searching through old logs, and unnecessarily worrying about whether they need to notify their entire customer base of a potential data leak.)

Even cartoons tell you to sanitize your SQL input.

Prepared statements

Why use prepared statements (in order of importance):
1. Improved correctness: They sanitize inputs for you.
2. Improved performance: The database-engine can optimize (compile) the query just once, and doesn't need to re-optimize every time you repeatedly-call the prepared query.
What they are: SQL statements where you replace specific values with a question-mark (“?”).
Example: SELECT * from Inventory where (product = ?).
Note:Don't confuse “prepared statement” with “stored procedure” — the latter is just a PL/SQL function. (They are similar though — both set up what is in essence a function-call, so you can just call it with actual-parameters later. However, a prepared-statement only lives while you keep a reference to it while the stored-procedure is something the database remembers forever (and is PL/SQL code, not a SQL query).

How to use:

In php, prepared statements are a bit convoluted. You have to:
1. Create the prepared statement (one-time setup).
  $stmt = mysqli_stmt_init($link); $stmt = mysqli_stmt_prepare($stmt, "SELECT numOnShelf,seller from Inventory where Product=? and containerSize>?");
  Since these two statements are commonly used together, there is another function that effects the exact same thing:
  $stmt = mysqli_prepare($link, "SELECT numOnShelf,seller from Inventory where Product=? and containerSize>?");
  Note that, out of all the functions handling prepared-statements, “mysqli_prepare” is the only name that name doesn't contain “_stmt_” nor require a statement-object as its first argument.
2. At this point, you'd think you're pretty much set, and you could do something reasonable like “~~$resultTable = wishfullyExecutePreparedStmt( $stmt, "M&M's", 16)~~”. Alas, You can't do this. For reasons I don't understand, we have to jump through further hoops: binding variables.
3. Bind php-variables to each the sql-parameters.
  (If your statement doesn't have any input-parameters “?”, you can skip this step.)
  In php, you must specify the type of each input (which is the second argument to mysqli_stmt_bind_param — s for string and d for integer, just like printf):
  mysqli_stmt_bind_param($stmt, 'sd', $productName, $minSize); $productName = "M&M's"; $minSize = 37;
  NOTE: this is very odd: The query will later remember to always use the contents of the particular variables $productName and $minSize, whan making the query! (In particular, ~~mysqli_stmt_bind_param($stmt, 'sd', "M&M's", 16);~~ does not work?!?!, since it passes literal strings/ints, rather than a raw variable.)
  
  Note that the contents of the input-variables (“productName” and “minSize” in this case) are SQL-sanitized for you! You don't have to call mysql_real_escape_string. (In fact, you definitely don't want to call that function if you're using prepared statements, since that would quote any quote-characters!)
4. Bind php-variables to hold the sql-result(s): mysqli_stmt_bind_result($col1, $col2);. After executing the statement (next step), the results will magically be stored into your variables $col1 and $col2.
5. Instead of getting the query-results back through bound-variables, we use the more conventional “loop through the rows of the result-table” approach, by using mysqli-stmt.get-result.php a method of the class mysqli-stmt. See the user-contributed comment in the php prepare documentation.
6. To actually run the query, try:
  mysqli_stmt_fetch($stmt) // If successful, puts the (next) result-row into the php variables $col1,$col2.
  If you are making this query multiple times, you presumably won't need to re-bind any variables; you'll just change the contents of the input-variables and then call mysqli_stmt_fetch, and it will use the revised values. (Weird — those bind-statements aren't any more programmer-friendly than just passing and returning values, but their call-by-name is certainly more paradigm-breaking.)
Close the statement: mysqli_stmt_close($stmt).

Securing the data stored in the database.
Suppose we store passwords or credit-cards in our database. What might someday happen (inferring from headlines)? Sadly, somebody might breach the db itself (aside from the website entirely) — either a hacker exploiting a DB security hole, or somebody accidentaly revealing the DB account password, or an inside employee stealing data.

Solution: Don't store the password in the database!
“But Barland, how the heck can we verify that they've typed in the correct password?”
We'll instead store a hashed form of the password. Then, when a user later types in the password on a form, we'll hash whatever they type, and see if the two hashes are equal.

php > echo hash('sha256', 'hello');
2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
php > echo hash('sha256', 'helmo');
05c21a42da66f4e2d217e35b74ef9dc2d65c8d46f9aec0d6397a65dd3c766f21

(Yes, theoretically, there is tiny (but non-quite-zero!) chance that two different strings might happen to encrypt to the same value. In practice, I would wait to win the lottery before I worry about two independently-chosen strings both happen to have hash-values that collide.)

Of course, to help protect against brute-force dictionary attacks, you want to add a few extra "salt" characters to the password — say, 20 characters. And ideally, these should be different characters for each person (else, if an attacker found out the one salt you used, they could use that on every word in the dictionary and look for matches). … But it's admittedly a bit of a hassle, to generate a salt for each user, and store that in the database as well as the hashed password. Solution: The function password_hash does it for you: it returns a hashed password concatenated to a random salt. (See the format of the result.) Then later, when a user returns, you can call password-verify, giving it the previous string, and it returns a boolean value:

         php > echo password_hash("kittee",PASSWORD_BCRYPT);
         $2y$10$Pj/26IW2mmF7LrA7WZGKeOf7YMIX5EKT7h8bhoBCwQs67PB5DDhUi
         php > echo password_verify("kittee",'$2y$10$Pj/26IW2mmF7LrA7WZGKeOf7YMIX5EKT7h8bhoBCwQs67PB5DDhUi');
         1
         php > echo password_verify("doge",'$2y$10$Pj/26IW2mmF7LrA7WZGKeOf7YMIX5EKT7h8bhoBCwQs67PB5DDhUi');
         php > echo password_hash("kittee",PASSWORD_BCRYPT);
         $2y$10$Ije9mY4Mm9LwEFWSySGBmOGjzg1/Y6VLYLPnsmWVuWHzoYdiz5lyG

For more details, read the php docs: us.php.net/manual/en/faq.passwords.php.

¹ Well, in this case, there are two other minor differences: to create the connection call “new mysqli(…)” instead of “mysqli_connect(…)”; and to check for errors look at the (string) field “$connect->connect_error” instead of checking for $connect being null. ↩

² A saucier attacker would type in something like “' or 'security' != 'good”. ↩

home—lects—exams—hws
D2L—breeze (snow day)

©2015, Ian Barland, Radford University
Last modified 2015.Nov.11 (Wed) Please mail any suggestions
(incl. typos, broken links)
to ibarlandradford.edu

mysqli MySQLchapter 12

Prepared statements

How to use:

mysqli
MySQL
chapter 12