Help w/ Prepared Statements

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Help w/ Prepared Statements

Post 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>

User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Help w/ Prepared Statements

Post by Celauran »

Where are $firstname, $lastname, and $email defined?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Help w/ Prepared Statements

Post 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);
(#10850)
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: Help w/ Prepared Statements

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Help w/ Prepared Statements

Post 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.
(#10850)
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: Help w/ Prepared Statements

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Help w/ Prepared Statements

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Help w/ Prepared Statements

Post 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.
(#10850)
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: Help w/ Prepared Statements

Post by diseman »

Thank you to both of you. :)
Post Reply