Article : Saving Signatures with PHP & MySQL

Saving Signatures with PHP & MySQL
A small PHP tutorial on how to capture the signature from Signature Pad and store it in a MySQL database, for later retrieval.

•Sample App ZIP�GitHub
This tutorial depends on Signature Pad. If you don’t know what that is go check out Signature Pad, for capturing electronic signatures.

This tutorial is just a small overview of the important pieces of information from the downloadable sample application. Check out the sample app itself, there are lots of comments in the code to help you out.

Sample Application Files
saving-signatures-sample/
 |- index.php
 |- lib/
 |  |- save-signature.php
 |- views/
 |  |- accept.php
 |  |- regenerate.php
 |- signature-pad/
 |  |- …•index.php contains the basic HTML template and a few if-statements to hide/show different pieces of HTML and Javascript.
•lib/save-signature.php is the meat and potatoes, the controller: it gets the content from the form, validates it, and saves it to the database.
•views/accept.php is the basic Signature Pad HTML template for accepting a signature, with the addition of some serverside validation code.
•views/regenerate.php is the basic Signature Pad HTML template for regenerating a signature, with the addition of a little PHP to output the signator and date.
•signature-pad is just a clone of the complete Signature Pad Git repository. The sample application only uses files from the build sub-folder.
Getting the Signature
Signature Pad submits the signature, along with the rest of the form submission, inside a hidden input field.

From views/accept.php:

<form method="post" action="index.php">
  â‹®
  <input type="hidden" name="output">
  â‹®
</form>
From this hidden field we can capture the signature and store it in the database.

The easiest way to get the signature using PHP is with the $_POST super global.

<?php
$sig = $_POST['output'];
Using the $_POST array isn’t secure (and creates a few other problems when there are validation errors and we try to keep information in the form). In PHP, the best way to get information from a form is using PHP’s filter functions. They provide a more secure way to grab user input and strip out unwanted information. We also won’t get any PHP error messages if we try to access the user input and the form hasn’t been submittted.

From lib/save-signature.php:

<?php
$output = filter_input(INPUT_POST, 'output', FILTER_UNSAFE_RAW);
We can use FILTER_UNSAFE_RAW for the signature itself, because we don’t actually want to strip any information from the signature. If you want to be even more specific try using FILTER_VALIDATE_REGEX.

Validating the Signature
Probably the best way to validate the signature would be to run it through json_decode() and see if it can be decoded.

From lib/save-signature.php:

<?php
if (!json_decode($output)) {
  $errors['output'] = true;
}
Don’t forget, you should also validate the name to make sure one was entered.

Setting Up the Database
The database to store the signature only needs to hold a few pieces of information: the signator’s name and the signature. For legal reasons, it is best to store more information about the signature: at least a hash of the signature, the signator’s IP address, and the time the signature was written.

For more information about electronic signature legallity, check out your country’s regulations. Wikipedia has a good list of electronic signature regulations.

Sample Table Setup
Name Data-Type Length What’s it For?
id int 11 Primary key, auto increment
signator varchar 255 Holds the user input for the `name` field
signature text  Holds the signature in its JSON form
sig_hash varchar 128 Holds a SHA1 hash of the JSON signature
ip varchar 46 Holds the signator’s IP address
created int 11 The UNIX timestamp of when the signature was saved

Saving to the Database
After everything is validated, we can save the signature to the database. It’s easiest to just store the JSON representation of the signature in the database. If you want to create a graphic file, check out how to convert the signature to an image.

It’s best to use PHP’s PDO for connecting to databases. By using PDO::prepare(), we can help protect against SQL injection attacks.

From lib/save-signature.php:

<?php
// Open the database connection
$db = new PDO($dsn, $user, $pass);
// Make sure we are talking to the database in UTF-8
$db->exec('SET NAMES utf8');

// Create some other pieces of information about the user
//  to confirm the legitimacy of their signature
$sig_hash = sha1($output);
$created = time();
$ip = $_SERVER['REMOTE_ADDR'];

// Use PDO prepare to insert all the information into the database
$sql = $db->prepare('
  INSERT INTO signatures (signator, signature, sig_hash, ip, created)
  VALUES (:signator, :signature, :sig_hash, :ip, :created)
');
$sql->bindValue(':signator', $name, PDO::PARAM_STR);
$sql->bindValue(':signature', $output, PDO::PARAM_STR);
$sql->bindValue(':sig_hash', $sig_hash, PDO::PARAM_STR);
$sql->bindValue(':ip', $ip, PDO::PARAM_STR);
$sql->bindValue(':created', $created, PDO::PARAM_INT);
$sql->execute();
Regenerating the Signature
One of the easiest ways to regenerate the signature is to use PHP to write out some Javascript onto one of your pages. When the page loads it will have a native Javascript varible containing all the signature information and can use Signature Pad to regenerate the display.

<script>
  $(document).ready(function () {
  // Write out the complete signature from the database to Javascript
  var sig = <?php echo $output; ?>;
  $('.sigPad').signaturePad({displayOnly : true}).regenerate(sig);
  });
</script>
What Else Do We Need?
Well, this application is far from complete�even though it’s fully functional. It’s a simple tutorial on how to capture the signature. Some of the missing things are:

1.User authentication, sign-in/sign-out, or unique passkeys.
2.HTTPS�critical for capturing signatures online.
3.It might be nice to encrypt the signatures in the database.