Canonical: How to save HTML form data into MySQL database

Question

This is a canonical question and answer for saving data from (HTML) form to MySQL database using PHP.

This applies to you if you are trying to do the following:

Accept user input on a HTML form
Process the input using PHP script
Store the said input into a MySQL database.

Examples of similar questions asked in the past that should not be used:

Connecting PHP Code and Submit Form to mySQL Database
Insert into mysql using php / html form - not working
How to use PHP to pass HTML form data to a MYSQL db and return the data to the browser
saving form data to database

In short, please read on if you questions is: I want to store user input to database using HTML forms, PHP and MySQL or similar.

Please add further examples on how to use checkboxes and other form elements. The idea is to close future duplicates against this question and update it if additional variations are needed. — vhu, Jun 16 '14 at 13:34
Most of your links contains the use of the deprecated `mysql_*` functions. You should have at least found some using `mysqli_*` functions. *Plus,* even better, ones using prepared statements. Such as [**`mysqli_*` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or **PDO** with [**prepared statements**](http://php.net/pdo.prepared-statements) — Funk Forty Niner, Jun 16 '14 at 13:36
@Fred-ii-: That's part of the reason why they are linked. SQL injections are covered in the answer. I guess I need to be more specific on the question part as well. — vhu, Jun 16 '14 at 13:39
This is what I answer people sometimes who think that using PDO is safe. ---=> "The use of `mysqli_` and/or PDO are not safeguards against SQL injection; not on their own that is. Using [**`mysqli_*` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or **PDO** with [**prepared statements**](http://php.net/pdo.prepared-statements) will." — Funk Forty Niner, Jun 16 '14 at 13:41

score 6 · Accepted Answer · edited May 23 '17 at 12:19

First of all your PHP or HTML page should produce a form that user can interact with. In the most simple form it'd be something like:

<html>
<body>
 <form method="post" action="yourscript.php">
  <input type="text" name="yourfield">
  <input type="submit" name="youraction" value="save">
 </form>
</body>
</html>

This will give your user a simple form with single input field and 'save' button. After clicking the 'save' button for content will be sent to your 'yourscript.php' using POST method.

yourscript.php should implement the following:

Accept and process the input from your form.
Connect to your MySQL database.
Store into the database.

In most simplistic form this would be:

<!doctype html>
<html>
 <head>
  <title>Process and store</title>
 </head>
<body>
<?php

// Check that user sent some data to begin with. 
if (isset($_REQUEST['yourfield'])) {

    /* Sanitize input. Trust *nothing* sent by the client.
     * When possible use whitelisting, only allow characters that you know
     * are needed. If username must contain only alphanumeric characters,
     * without puntation, then you should not accept anything else.
     * For more details, see: https://stackoverflow.com/a/10094315
     */
    $yourfield=preg_replace('/[^a-zA-Z0-9\ ]/','',$_REQUEST['yourfield']);

    /* Escape your input: use htmlspecialchars to avoid most obvious XSS attacks.
     * Note: Your application may still be vulnerable to XSS if you use $yourfield
     *       in an attribute without proper quoting.
     * For more details, see: https://stackoverflow.com/a/130323
     */
    $yourfield=htmlspecialchars($yourfield);


} else {
    die('User did not send any data to be saved!');
}


// Define MySQL connection and credentials
$pdo_dsn='mysql:dbname=yourdatabase;host=databasehost.example.com';
$pdo_user='yourdatabaseuser';     
$pdo_password='yourdatabaspassword';  

try {
    // Establish connection to database
    $conn = new PDO($pdo_dsn, $pdo_user, $pdo_password);

    // Throw exceptions in case of error.
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Use prepared statements to mitigate SQL injection attacks.
    // See https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php for more details
    $qry=$conn->prepare('INSERT INTO yourtable (yourcolumn) VALUES (:yourvalue)');

    // Execute the prepared statement using user supplied data.
    $qry->execute(Array(":yourvalue" => $yourfield));

} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage() . " file: " . $e->getFile() . " line: " . $e->getLine();
    exit;
}
?>
<form method="post">

 <!-- Please note that the quotes around next <?php ... ?> block are important
      to avoid XSS issues with poorly escaped user input. For more details:
      https://stackoverflow.com/a/2894530
  -->
 <input type="text" name="yourfield" value="<?php print $yourfield; ?>">
 <input type="submit" name="youraction" value="save">
</form>

</body>
</html>

Key takeaway here is to use prepared statements to avoid SQL injections attacks.

This `('INSERT INTO yourtable VALUES (:yourvalue)')` needs to be changed to `('INSERT INTO yourtable (your_column) VALUES (:yourvalue)')`. It's best to specify actual columns to insert into. — Funk Forty Niner, Jun 16 '14 at 13:44
And where is the processing part? I would have expected a reference answer to contain some elaboration on charset validation, text/* constraints, or e.g. HTMLPurifier at least mentioned. — mario, Jun 16 '14 at 14:47

Canonical: How to save HTML form data into MySQL database

1 Answers1

Linked