<? Updating via Forms?>

Form CRUD

Thus far when we have worked with data in our database it has been to read data via a select statement and place database data on our pages.  We're now going to explore ways to update that data via HTML forms.

Forms Change Data: When users interact on a website they frequently do so by inputting data via forms.    Everything from logging into a member area, or making a purchase are done via a form.  Sometimes they enter new data, and sometimes they update existing data.  When we are logged in as website administrators we may even delete data.  Here are examples of all three types of SQL statements:

Insert Statements: When a user registers to become a member at a website, or makes a purchase, new data is potentially entered into a database.  When new data is entered, we use an insert SQL statement, for example:

INSERT INTO tblCustomers (FirstName,LastName) VALUES ('Bilbo','Baggins');

Note that there is no reference to CustomerID, since when data is entered a primary key like CustomerID is entered automatically. 

Update Statements: When a user updates their registration on a website, existing data is potentially updated in a database.  We would use an update SQL statement, for example:

UPDATE tblCustomers SET FirstName='Frodo' WHERE CustomerID = 21;

Note the requirement of the CustomerID (primary key) to be sure we are fully identifying which customer's data to change. 

Delete Statements: When an administrator needs to delete potentially flawed data, they can use a delete SQL statement, for example:

DELETE FROM tblCustomers WHERE CustomerID = 21;

This will delete the entire row of data.  It is normally not a good policy to allow our clients/admins delete data too easily.  If records in a database table are related to other tables, a casual deletion of data can cause orphaned records, (records that were once related to a different table, but now have lost their context).  It is usually better to build your application in such a way that old data can be stored in a separate table, or have a field (named Status, perhaps?) which can store values like "current" and "archived" so our clients don't see old data.

All That CRUD: Now that we've talked about inserting updating and deleting data, we can add the select statement that we are familiar with and discuss what is now called CRUD on our database data.  CRUD is developer jargon referring to our topics of the day as follows:

  • Create == Insert
  • Read == Select
  • Update == Update
  • Delete == Delete

Using the acronym CRUD helps developers keep in mind that we will likely need to develop our application so that our clients can perform all 4 of the CRUD operations on any relevant data.

Performing CRUD via PHP: We can insert, update or delete database data via PHP's mysql classic or mysqli improved connections.  We use a the SQL statement and the mysql_query() or mysqli_query() functions to run our SQL against the database:

mysqli_query($iConn,$sql);

Note there is no $result returned in the example above.  Only select statements return an array of data, not inserts, updates or deletes.  However, we can determine if there was an error changing the data by querying the $result of our CRUD statement:

$result = mysqli_query($iConn,$sql);
if(!$result){die("Something went wrong! " . $mysqli_error($iConn));}

Besides determining if something 'went wrong' during our query, we can also determine how many records were affected.  This is useful if we want to know how many rows were deleted, etc:

echo 'There were <b>' . mysqli_affected_rows($iConn) . '</b> item(s) deleted.<br />';

In the above we check the connection for the last operation performed and use the mysqli_affected_rows() function to determine the affect. 

Getting the ID number on insert: There is one additional item we can get from a database connection on insert only.  When we insert data, we frequently need to know the new ID number created upon insertion.  To find this out we need to use a special function named mysqli_insert_id():

$myID = mysqli_insert_id($iConn);

We can then use this information if we need to update further records.

Integrating Form Data: When we build a web application we'll frequently build our SQL statement out of a combination of form and perhaps session data. 

POST Data: When we build a form and insert the value POST into the method attribute of our form, we'll be able to retrieve data via PHP's $_POST superglobal variables once the form is submitted.  If we require a unique ID number to identify the user, we could retrieve this from a session variable:

$sql = "UPDATE tblCustomers SET FirstName = '" . $_POST['FirstName'] . "' WHERE CustomerID = " . $_SESSION['CustomerID'];

This highlights the importance of storing the ID number that follows a logged in user.  We use this ID number to identify the user to be able to update the appropriate record.

Data Validation: When we allow a web application to insert or update data there's a great possibility of damage.  This can be purposeful in the case of hacking, for example with sql injection, or even accidental, when a customer types their last name as O'Malley and the single quote breaks the SQL string.

In either case it's not sufficient to allow JavaScript validation as our only means of data validation.  JavaScript is client side and can be shut off and easily bypassed.  We'll need to use PHP & MySQL on the server side to validate protect and perhaps clean or alter data coming into our database.

Casting Numerical Data: Numerical data is the easiest data type to handle on insert/update.  We can run numerical data through a type cast and forcibly convert the data to the data type we expect:

$myID = (int)$_POST["CustomerID"];

Any data that is not convertable to an integer will be turned to zero.  This is much preferable than any hacker bit, and in fact supports the happy accident that zero can't be related to any table as an ID!

Filtering String Data: When the data we expect is of the string data type the least we can do is use PHP's addslashes() function to the data. addslashes() adds a backslash to every single quote, escaping the character and preventing the destruction of the SQL statement for names like O'Malley.

$myName = addslashes($_POST["FirstName"]);

Using addslashes() on entry to the database requires using stripslashes() on retrieval to remove the slashes added for our protection. 

What if we don't want our users to enter HTML?  We can use strip_tags()

$myName = addslashes(strip_tags($_POST["FirstName"]));

Above we are using both functions together.

Escaping Strings: It is not longer advised to use addslashes() only, as there are other dangerous characters besides the single quote.  It is currently advised by the PHP folks to use mysql_real_escape_string() or mysqli_real_escape_string() instead of addslashes().  Not only will it 'add the slashes' but it will escape other characters as well:

$myName = mysqli_real_escape_string($_POST["FirstName"]);

Escape vs Real Escape: PHP had an earlier version of mysql_real_escape_string() called mysql_escape_string().  If you look at the documentation for mysql_escape_string() you'll see it is 'deprecated, and it's use is highly discouraged'.

Wrapper Functions: This is significant to us because we know this function was deprecated in favor of mysql_real_escape_string(), which is a similarly named but entirely different function.  Due to this and the potential of emerging input vulnerabilities we'll wrap the preferred functions into our own versions, dbIn() and idbIn(), respectively.  Using our own wrapper functions and placing them inside common_inc.php allows us to change the behavior across entire applications from one place.  Here's how we can use iDBIn() the wrapper for msqli_real_escape_string():

$myName = iDBIn($_POST["FirstName"]);

formReq & iformReq: We have two more wrapper functions to help make entering form data safe and easy.  We'll still use dbIn() and idbIn() but wrap them inside two additional functions, formReq() and iformReq().  That is req is as in required. See how they can be used:

$redirect = THIS_PAGE; //assumes postback application
$myVar = iformReq('myVar',$iConn);
$otherVar = iformReq('otherVar',$iConn); 

Only the name of the POST variable is passed to the function, along with an existing MySQLi connection in the variable named $iConn.

We can also declare a variable named redirect which is where the user will be sent upon failure.  This variable is made global inside the iformReq() function so it only needs to be declared once per page.  Internally iformReq() will both check to be sure data was sent via POST and redirect if it was not (or was empty) and will also pass the data off to idbIn() for processing. 

Parameterizd Queries with sprintf():  There is another advised measure to take to be sure that data being sent via a form has not corrupted.  We can run our SQL statement through PHP's sprintf() function.  View the following:

$sql = sprintf("UPDATE Customers SET FIRSTNAME = '%s' WHERE CustomerID=%d",$FirstName,(int)$id);mysqli_query($iConn,$sql);

There's a lot going on above.  There is variable replacement being used in the SQL statement, enabled by the double quotes.   Using double quotes also allows us to use single quotes inside the SQL statement without any escaping.  There are two further sprintf() replacements being used, %s which will only accept a string, and %d which will only accept a number.

Inside our call to the sprintf() function we see 3 parameters.  The first is our SQL statement as a string (with our replacements) plus our two variables ($FirstName and $id) as additional parameters.  The key is that the items being added in the parameters after the SQL statement are used to 'fill in' the replacements.  Since there are two replacements (%s and %d) there need to be two additional parameters ($FirstName and $id).  We can filter any number of parameters in this way.

The above is PHP's version of what is called a parameterized query.  The  SQL statement (the query) is loaded with parameters of a specific type, and each parameter is filtered for a specific type by sprintf()

 

Print this Page Back To Top

© 2000- 2014 newMANIC INC, All rights reserved