Page 1 of 1

Help w/ Prepared Statements

Posted: Tue Mar 29, 2016 7:42 pm
by diseman
Hi,

So, a few months ago Celauran suggested I start learning Prepared Statements. Today, I spent a lot of time looking around, but can't seem to find a simple example that shows how to insert data from a form using prepared statements. So, I created my own. I was able to use one example I found to get data into a db, but when I removed the hard-coded fields and put in my form fields, the data doesn't save to the db.

Could someone please take a look at my simple learning script and fix it to make it work, so I can learn and build upon it? Thanks in advance!

Code: Select all


<?php

$servername = "localhost";
$username = "admin";
$password = "admin";
$dbname = "prepared_statements";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


// ****************************************************************************

$sql = "DROP TABLE if exists MyGuests" ;

mysqli_query($conn, $sql);

$sql = "CREATE TABLE MyGuests (

firstname varchar(32),
lastname varchar(32),
email varchar(128),

PRIMARY KEY  (email)

)";

mysqli_query($conn, $sql);

echo "MyGuests Successfuly Created<br><br>";

// ****************************************************************************

echo "<pre>"; print_r($_POST); echo "</pre>";

if(isset($_POST['save'])) {

     // prepare

          $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");

     // bind

          $stmt->bind_param("sss", $firstname, $lastname, $email);

     $stmt->execute(); // not sure if this is suppose to be here or not

}

$stmt->close();
$conn->close();

?>

<form name="form" id="form" method="post">

<table style="width: 90%; padding: 5px; border-spacing: 15px;">
	<tr>
		<td style="width: 200px; white-space: nowrap;">First Name</td>
		<td><input name="firstname" value="<?=$firstname;?>"></td>
	</tr>
	<tr>
		<td>Last Name</td>
		<td><input name="lastname" value="<?=$lastname;?>"></td>
	</tr>
	<tr>
		<td>Email Address</td>
		<td><input name="email" value="<?=$email;?>"></td>
	</tr>
     <tr><td><input type="submit" name="save" value="Save Entries"/></td>
     </tr>
</table>

</form>


Re: Help w/ Prepared Statements

Posted: Tue Mar 29, 2016 8:37 pm
by Celauran
Where are $firstname, $lastname, and $email defined?

Re: Help w/ Prepared Statements

Posted: Tue Mar 29, 2016 10:49 pm
by Christopher
Yes, you need to get the variables from the form:

Code: Select all

$firstname = filter_input(INPUT_POST, 'firstname', FILTER_SANITIZE_STRING);
$lastname = filter_input(INPUT_POST, 'lastname', FILTER_SANITIZE_STRING);
$email = filter_input(INPUT_POST, 'email', FILTER_SANITIZE_EMAIL);

Re: Help w/ Prepared Statements

Posted: Wed Mar 30, 2016 6:51 am
by diseman
Thank You Christopher.

In all the examples of prepared statements I could find last night, I never saw that in the code.

It's working now.

Re: Help w/ Prepared Statements

Posted: Wed Mar 30, 2016 2:58 pm
by Christopher
Note that I am using filter_input(). Beware of anything coming from the browser i.e., $_POST, $_GET, $_REQUEST, $_COOKIE, etc. Even $_SERVER has values that come from the browser, so beware of those as well. All external values should be Validated, Filtered/Sanitized and Escaped before using in SQL, HTML, JSON, etc.

Re: Help w/ Prepared Statements

Posted: Wed Mar 30, 2016 5:55 pm
by diseman
Do you by any chance have any examples or Bookmarks/Favorites that might show a simple form that does it 100% correctly and accounts for everything you said?

Would be nice to find just one example somewhere that shows even just two fields properly being submitted to a database.

Thanks

Re: Help w/ Prepared Statements

Posted: Wed Mar 30, 2016 7:31 pm
by Celauran
There's a good example of using prepared statements here: http://www.phptherightway.com/#pdo_extension
Another for data filtering: http://www.phptherightway.com/#data_filtering

Those really are the two bits you need to be concerned with, though validation can certainly go beyond validation filters described there.

Re: Help w/ Prepared Statements

Posted: Thu Mar 31, 2016 10:22 am
by Christopher
I don't think it is possible to have just one example, because Validation and Filtering/Sanitizing are dependent on the type of data. The general approach is the same. However, integers, floats, simple strings (e.g. names), special strings (e.g., email, phone, credit card number), long text (with double quotes and any punctuation), etc. -- they all have slightly different needs.

And it depends on what you are going to do with the value. To be used in SQL, it should be escaped using prepared statements. To be output in HTML, use htmlspecialchars(), other outputs may require addslashes() or addcslashes().

As a general rule, follow Defense in Depth which says to implement security at every level of your code and not depend on an outer defense -- so no code should trust any data it receives.

Re: Help w/ Prepared Statements

Posted: Thu Mar 31, 2016 10:30 am
by diseman
Thank you to both of you. :)