如何将PDF签名写入MySQL数据库

Question

I have developed a PDF Form my company uses to keep track of mileage expenses. When the user is finished, the user submits the document by clicking a button. Once submitted, it posts to a php web page and writes the field information to a MySQL Database. This is great and all, but we are required to have users sign the document using a PDF electronic signature field. I cannot get the signature to post, or write to the database. The field name is signature. Any assistance would be great.

The code is below (without the signature):

<?php

    include_once('../php/functions.php');

    $sig_month = $_POST['sig_month'];
    $sig_day = $_POST['sig_day'];
    $sig_year = $_POST['sig_year'];
    $mileage_rate = $_POST['mileage_rate'];
    $form_id = $_POST['form_id'];
    $submission_date = $_POST['submission_date'];
    $signed = $_POST['signed'];
    $employee_name = $_POST['employee_name']; 

    if(strlen($sig_month) == 1){
        $sig_month = "0".$sig_month;
    }
    if(strlen($sig_day) == 1){
        $sig_day = "0".$sig_day;
    }

    $employee_initial = $employee_name[0];
    $employee_explode = explode(" ", $employee_name);
    $employee_email = $employee_initial . $employee_explode[1];
    $employee_email = strtolower($employee_email) . "@fbhi.net";

    $mileage_rate = "0.".$mileage_rate;

    $query="SELECT * FROM employee WHERE email = '$employee_email'";
    $ifExists = ifExists($query,$db_con);   

    if($ifExists > 0){

        $result = queryMysqli($query,$db_con);

        while($row = mysqli_fetch_array($result)){

            $employee_name = $row['first_name'] . " " . $row['last_name'];
            $error = 0;
            $success = 0;
            $x = 1;

            $query1 = "DELETE FROM mileage_form WHERE form_id = '$form_id'";
            if(!queryMysqli($query1,$db_con)){

                $error++;

            }           

            while($x < 8){

                $month = $_POST['month_day'.$x];
                $day = $_POST['day_day'.$x];
                $year = $_POST['year_day'.$x];
                $purpose = $_POST['purpose_day'.$x];
                $miles = $_POST['miles_day'.$x];

                if(strlen($month) == 1){
                    $month = "0".$month;
                }
                if(strlen($day) == 1){
                    $day = "0".$day;
                }

                if($employee_name != "" && $mileage_rate != "" && $month != "" && $day != "" && $year != "" && $purpose != "" && $miles != "" && $form_id != "" && $submission_date != "" && $sig_month != "" && $sig_day != "" && $sig_year != "" && $signed == "t"){

                    $query = "INSERT INTO mileage_form (employee_name,mileage_rate,month,day,year,purpose,miles,sig_month,sig_day,sig_year,submission_date,signed,form_id)
                    VALUES ('$employee_name','$mileage_rate','$month','$day','$year','$purpose','$miles','$sig_month','$sig_day','$sig_year','$submission_date','$signed','$form_id')";
                    if(!queryMysqli($query,$db_con)){
                            $error++;
                        }else{
                            $success++;
                        }

                }
                $x++;
            }

            if($error != 0){
                echo "Something went wrong! Your mileage claims did not submit!";
            }

            if($success != 0){
                echo "<center>Mileage claims have been submitted for <br /><font size=6><strong>$employee_email</strong/></font><br />If this is not the correct email, contact your IT Administrator immediately!";
            }else{

                echo "Your mileage form is incomplete. Ensure all of the required information is finished, and then resubmit your form.";

            }


        }

    }else{

        echo "<center>Sorry, your email is not registered in our database. If you are positive your email is $employee_email, please contact your IT Administrator";

    }
?>

Answer 1

The PDF form at hand is a regular (i.e. non-XFA) PDF form submitting in HTML Form format.

For forms submitted like this submitting the contents of signature fields would make no sense and, therefore, is not done.

Thus, to have access to signature information you have to change the form to submit as a whole PDF (with filled-in fields) or to submit as FDF using IncludeAppendSaves.

Alternatively you may consider XFA forms and XFA signatures signing form data only.

In any way you'll have to rewrite both the PDF and the form data retrieval.

Some details:

Signature values make no sense in HTML Form format submits

Digital signatures usually sign some byte array. In case of integrated PDF signatures this byte array consists of the whole PDF merely excluding the range reserved for the signature itself:

(For more details and pointers to documents covering this in depth cf. this answer.)

In case of the document at hand these signed byte ranges not only include the original PDF and the form fill-ins but also new PDF metadata and signing details. When you retrieve the form values via HTML Form format you do not have access to these metadata: e.g. you don't know the exact modification datetime or the newly generated PDF document identifier (stored in the metadata).

Thus, that signature alone is of no value to you because you don't have the originally signed document to validate the signature. Thus, anyone could simply copy a signature from some other document and send it along, and you wouldn't have a chance to recognize the fraud.

Solution 1: Sending the whole PDF

The PDF submit-form action allows you to request sending the whole PDF instead of merely the form field values. Cf. the SubmitPDF flag explained in Table 237 Flags for submit-form actions in the PDF specification ISO 32000-1:

If set, the document shall be submitted as PDF, using the MIME content type application/pdf (described in Internet RFC 2045, Multipurpose Internet Mail Extensions (MIME), Part One: Format of Internet Message Bodies; see the Bibliography).

As you get the whole PDF, you can extract all form values including the signature using normal PDF libraries, and you also can verify the signature (and so positively identify the person who filled in that form).

The obvious draw-back is the amount of data transferred.

Solution 2: Sending the PDF update section

The PDF file format allows changes of a file to be appended to its end without the need to change the original file contents:

Using this technology it would suffice in the case at hand to forward only the additions which mainly consists of the form fill-ins and the signature.

The PDF submit-form action allows you to request this. Cf. the IncludeAppendSaves flag explained in Table 237 Flags for submit-form actions in the PDF specification ISO 32000-1:

If set, the submitted FDF file shall include the contents of all incremental updates to the underlying PDF document, as contained in the Differences entry in the FDF dictionary (see Table 243).

In Table 243 Entries in the FDF dictionary you even find this hint containing signatures:

This allows any digital signatures (see 12.8, “Digital Signatures”) to be transmitted to the server.

In contrast to solution 1 this requires transmission of a fairly small amount of data. The transmitted data, though, are in FDF, a format similar but not identical to PDF. I don't know which libraries are available to you for FDF reading.

Solution 3: Using XFA form technologies

Yet another solution is to switch from regular PDF forms to XFA (Adobe XML Form Architecture) forms. Essentially this uses XML as format for holding form information and allows XML signatures to sign merely the XML, not all of the PDF. This may give rise to a solution with fairly little bandwidth use which is built on standard XML technologies.

I don't know much about XFA, though, and cannot go into detail.

A dirty, half-hearted Work-Around

If it turns out that you cannot access posted PDF or FDF data (I don't know how cooperative PHP is in this respect) and you also are not really interested in verifying the signature but merely getting one to put into the database, you may try and use JavaScript in the PDF to deep copy the contents of the signature field, text-encode it, and put it into some hidden text field.

This way you would get the signature from that hidden field submitted in HTML Form format. You merely could not verify whether it actually signed the form. ;)