CODEWITHSUNDEEP
phpmysqlpdoprepared-statementinsert-into
user4324916
user4324916

Reputation:

Creating a Form to POST into MySQL using PDO properly?

I'm trying to use a PDO Wrapper Class and in general for proper security against SQL injections as good practice. Trying to learn the clean bare essentials for filling out a form to POST into MySQL.

So, at one point my form was inserting data into the MySQL table, but was doing multiple records on refresh after submit. Then I researched a cleaner way to write the processor but am now having trouble making it insert into the table. Not sure if maybe there is a discrepancy between the Processor and the "class.db.php" file?

I've searched a lot for "how-to's" having no success in a consistent answer. Trying to understand what I'm doing wrong, desiring an answer of best practice. Everything I've seen is all over the map.

Here's where I'm at: For reference I started here first http://webdevelopingcat.com/php-mysql-tutorial-for-beginners-inserting-rows-with-pdo/

Then at top of the document I'm Including if you google, the https://code.google.com/p/php-pdo-wrapper-class/ project for a basis of class implementation.

<?php
include("class.db.php");
$version = "1.0.2";
$released = "December 9, 2010";
?>

Then a simple form within the body.

<?php
if ( empty( $_POST ) ){
?>

<form name='registration' action='success.php' method='POST'/>
<label for 'FName'>First Name: </label>
<input type="text" name="FName" />

<label for 'LName'>Last Name: </label>
<input type="text" name="LName" />

<label for 'Age'>Age: </label>
<input type="number" name="Age" />

<label for 'Gender'>Gender: </label>
<input type="text" name="Gender" />

<button type="submit">Submit</button>
</form>

Finally the form processor also within the body.

<?php
} else {
//process the form here
//
// Connect to database
$db = new db("mysql:host=localhost;dbname=pdodb", "root", "root");

$form = $_POST;
$first = $form[ 'FName' ];
$last = $form[ 'LName' ];
$myage = $form[ 'Age' ];
$gen = $form[ 'Gender' ];

$sql = "INSERT INTO mytable ( FName, LName, Age, Gender ) VALUES ( :first, :last, :myage, :gen )";

$query = $db->prepare( $sql );
$query->execute( array( ':first'=>$first, ':last'=>$last, ':myage'=>$myage, ':gen'=>$gen ) );

}
?>

The MANUAL way works. Referenced culttt.com post about: prevent-php-sql-injection-with-pdo-prepared-statements

// Create array of data to insert
$insert = array(
"FName" => "John",
"LName" => "Doe",
"Age" => 26,
"Gender" => "male"
);
// Insert the array into the table
$db->insert("mytable", $insert);

Upvotes: 0

Views: 7861

Answers (2)

Vivekanand Saraswati
Vivekanand Saraswati

Reputation: 21

<?php
// Get POST data
$first = (!empty($_POST['FName']) ? $_POST['FName'] : '');
$last = (!empty($_POST['LName']) ? $_POST['LName'] : '');
$myage = (!empty($_POST['Age']) ? $_POST['Age'] : '');
$gen = (!empty($_POST['Gender']) ? $_POST['Gender'] : 0);

try {
    // Connect to db
    $db = new PDO('mysql:dbname=pdodb;host=localhost', 'root', 'root');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Set SQL
    $sql = 'INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:first, :last, :myage, :gen)';
    // Prepare query
    $query = $db->prepare($sql);
    // Execute query
    $query->execute(array(':first' => $first, ':last' => $last, ':myage' => $myage, ':gen' => $gen));
    $db= null;
} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage();
    $db= null;
}

Upvotes: -2

versalle88
versalle88

Reputation: 1137

Your form is posting to success.php, so make sure that the insert code is in the success.php file:

<?php
// Get POST data
$first = (!empty($_POST['FName']) ? $_POST['FName'] : '');
$last = (!empty($_POST['LName']) ? $_POST['LName'] : '');
$myage = (!empty($_POST['Age']) ? $_POST['Age'] : '');
$gen = (!empty($_POST['Gender']) ? $_POST['Gender'] : 0);

try {
    // Connect to db
    $db = new db('mysql:dbname=pdodb;host=localhost', 'root', 'root');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Set SQL
    $sql = 'INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:first, :last, :myage, :gen)';
    // Prepare query
    $query = $db->prepare($sql);
    // Execute query
    $query->execute(array(':first' => $first, ':last' => $last, ':myage' => $myage, ':gen' => $gen));
} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage();
}

Thanks,

Andrew

Upvotes: 3

Related Questions