ITEC 325 2011spring ibarland

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

ch12-mysql
MySQL
chapter 12

From PHP Visual Quickstart Guide by Larry Ullman
Based 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.
• PHP's mysql-query function is the most-used command. It sends an SQL command to MySQL, and returns the result from the DB:
  $result = mysql_query(SQL statement);
  However, before we do this, we need to connect 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 = mysql_connect(hostname, username, password); // @param hostname For php.radford.edu, use 'localhost' // @param username your RU userid // @param password the *database* password.

  • Once connected, you should select the database schema — the schema name is also your RU id:
    mysql_select_db('userid');
    (You can see by this naming convention, that RU gives you only one schema!)
  • Eventually, when you are done working with the database, be sure to close the connection!
    mysql_close();
    (Inadvertent denial-of-resource attack: students create connections w/o closing them, and the DB server gets clogged up!)
• 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 hve 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.)

• 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 -
    

    $qu = "INSERT INTO $tablename (field1,field2,field3) VALUES (0,'abc',$var)"; $result = mysql_query($qu);

  • SELECT -
    

    $qu = "SELECT * FROM $tablename WHERE (field_name1 = 'abc')"; $result = mysql_query($qu);

    After execution of this query, most often $result will be an array of returned records. Usually, a single record at a time (of this result recordset) will have to be processed.
    

    while ($row = mysql_fetch_array($result)) { # Note use of `=` for assignment *and* return value echo "$row[0], $row[1]"; # in the above $row[0], refers to the value of the first field in the record # and $row[1] refers to the value in the second field in the record }

  • DELETE -
    

    $qu = "DELETE FROM $tablename WHERE (field1 = 'abc')";

    To see if a DELETE query worked, you can use the mysql_affected_rows()
    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')"; $result = mysql_query($qu);

    Remember that the most common use of SQL's UPDATE and DELETE is to provide a WHERE clause which uses the table's primary key, to target one row.
  • Example: ch12-mysql-example.php
• Constraints in mysql

  If you want to add or drop FK constraints in mysql: When creating a table, you can specify some Foreign Key (FK) constraints (apparently: from one column to a primary-key column). If you want to have a composite FK involving two columns, or a FK to a non-PK column, you can't just click the myphpAdmin GUI; you'll have to issue the SQL commands. (Note that myphpAdmin includes a pane for typing raw SQL commands, if you don't want to include it in php program.)

  -- 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_FK -- it seems odd to me, to say 'drop foreign key ...' rather than 'drop constraint ...'.

• 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'"; if (mysql_query($qry)) { // 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 e'lan — -- why will the query fail?
  WORSE: Can the user type in something so that the SELECT statment always returns a result?
  Alas, yes! (See Example #2 of mysql-real-escape-string.)
  To guard against this, always call mysql-real-escape-string on any user-provided string that is getting integrated into a query the database.

  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 mysql_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 mysql_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.

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

©2011, Ian Barland, Radford University Last modified 2011.Apr.15 (Fri)

Please mail any suggestions (incl. typos, broken links) to iba�rlandrad�ford.edu