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 test_Customers (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 anupdate SQL statement, for example:
UPDATE test_Customers 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 test_Customers 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:
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 effect.
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 namedmysqli_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 test_Customers 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"]);
Wrapper Functions: Due to the potential of needing several functions to run on each and every piece of data, as well as the possibility of new vulnerabilities, we'll wrap our preferred functions into our own custom functions, dbIn() and dbOut(), respectively. Using our own wrapper functions and placing them inside common.php allows us to change the behavior across entire applications from one place. Here's how we can use dbIn() the wrapper for msqli_real_escape_string():
$myName = dbIn($_POST["FirstName"],$iConn);
Requiring Form Elements: Another thing we can do is require specific $_POST data to be sent. This ensures our code will behave properly and can also check to be sure the data was sent by us and is not suspicious or malformed. Below is a custom function named required_params() which accepts an array of $_POST element names that are required for our specific form:
function required_params($params) {
foreach($params as $param) {
if(!isset($_REQUEST[$param])) {
return false;
}
}
return true;
}
Here is an example of the function above in use:
$params = array('last_name','first_name','email');#required fields
if(!required_params($params))
{//abort - required fields not sent
header('Location:index.php');#safe redirect location
die(); #halt page execution
}
Note the three $_POST elements required are last_name, first_name & email. Any optional fields (such as comments) would not be one of the parameters as it is not required.
Parameterized 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 an example of 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(). This disallows the injection of SQL commands and requires data into specific parameters instead.
Parameterized Queries with prepared statements: There is currently a better way to perform a parameterized query using a mysqli connection, it’s called a prepared statement.
When we use prepared statements we put a question mark as a placeholder for data to be inserted into a query. View the following:
$sql = "INSERT INTO test_Contacts (Name,Email,Comments,DateAdded) VALUES(?,?,?,NOW())";
$stmt = mysqli_prepare($iConn,$sql);
In the example above, we create a SQL statement in a variable but place question marks where each data item goes. Also note we don’t place quote marks around the question marks as we’ll identify the data type momentarily. Then we use a new function, mysqli_prepare() which is part of a multiple step process to prepare the data for insertion.
Next we bind three post data items (Name, Email and Comments) as variables to the statement in place of the question marks:
mysqli_stmt_bind_param($stmt, 'sss',$Name,$Email,$Comments);
Note the string of 3 s’s highlighted above. The ‘s’ stands for string and all three in a row indicate the data type and handling of each individual item. Here is some more info about how different data types would be represented:
/*
* second parameter of the mysqli_stmt_bind_param below
* identifies each data type inserted:
*
* i == integer
* d == double (floating point)
* s == string
* b == blob (file/image)
*
*example: an integer, 2 strings, then a double would be: "issd"
*/
Now the statement is prepared and it can be run against the database:
//execute sql command
mysqli_stmt_execute($stmt);
//close statement
@mysqli_stmt_close($stmt);
//close connection
@mysqli_close($iConn);
If we’re using mysqli type connections, it’s advised to use prepared statements whenever we’re accepting data from a public facing form such as a contact form, login page, etc.
PDO and prepared statements: The other viable connection type uses PDO, which we’ll go into at a later time. PDO utilizes a connection type that is not database specific and the way to create parameterized queries and prepared statements is very different using PDO. Here’s some info: