Newbie18 Newbie18 -4 years ago 61
MySQL Question

Prepared statement PDO gives NULL result n DB

I've developed a form in php which would allow the user to submit a simple job application.
Before sending the user input to my database I santize and validate my data.

<?php
$geslachtErr = $voornaamErr = $familienaamErr = $emailErr = $telErr = $afileErr = "";
$geslacht = $voornaam = $familienaam = $email = $tel = $afile = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {

if (empty($_POST["geslacht"])) {
$geslachtErr = "geslacht is required";
} else {
$geslacht = test_input($_POST["geslacht"]);
}

if (empty($_POST["voornaam"])) {
$voornaamErr = "voornaam is required";
} else {
$voornaam = test_input($_POST["voornaam"]);
// check if voornaam only contains letters and whitespace
if (!preg_match("/^[a-zA-Z ]*$/",$voornaam)) {
$voornaamErr = "Only letters and white space allowed";
}
}


The function that I use to sanitize the user input looks like this:

function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}


My html look like this:

<form method="post" enctype="multipart/form-data" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?> >


<div class="top-row">
<div class="field-wrap">
<p><span class="error">* verplichte velden.</span></p>
<label>Geslacht</label><span class="error">* <?php echo $geslachtErr;?></span>
<select name="geslacht" value="<?= isset($_POST['geslacht']) ? $_POST['geslacht'] : ''; ?>">>
<option value=""></option>
<option value="man">man</option>
<option value="vrouw">vrouw</option>
</select>
</div> <!-- /field-wrap-->

<div class="field-wrap">
<label>Voornaam</label><span class="error">* <?php echo $voornaamErr;?></span>
<input type="text" name="voornaam" value="<?= isset($_POST['voornaam']) ? $_POST['voornaam'] : ''; ?>">
</div> <!-- /field-wrap-->


If I than do a test and
echo
all my fields in the same form I get all the results.

But I want this data to be sent to a mysql db.
So I use PDO with Prepared Statement. This file is called dbcon.php

<?php
$servername = "xxxxxx";
$username = "xxxxxx";
$password = "xxxxx";

try {
$conn = new PDO("mysql:host=$servername;dbname=xxxxx", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare sql and bind parameters
$stmt = $conn->prepare("INSERT INTO sollicitatie_form (geslacht, voornaam, familienaam, email, tel)
VALUES (:geslacht, :voornaam, :familienaam, :email, :tel)");

$stmt->bindParam(':geslacht', $geslacht);
$stmt->bindParam(':voornaam', $voornaam);
$stmt->bindParam(':familienaam', $familienaam);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':tel', $tel);

$stmt->execute();


echo "New records created successfully";
}
catch(PDOException $e)
{
echo "Error: " . $e->getMessage();
}

$conn->null;


Now this is for sure where I'm going wrong, but I just can't figure out what I'm doing wrong.

When I want to sent the user input to my mysql db I change the action in my html from

<form method="post" enctype="multipart/form-data" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?> >


to

<form method="post" enctype="multipart/form-data" action="dbcon.php >


When I than submit data I get all "NULL" in my database...

The connection with my mysql db is working. I've double tested it.
I'm pretty sure that the sanatizing and validation is correct as when I test it locally (without sending it to mysql db) I get all the correct info.

So what am I doing wrong?

Answer Source

So what am I doing wrong?

don't store things pre-encoded in the db. and for that matter, don't sanitize input, validate & encode it. and don't call it test_input if you really mean sanitize_input - instead, call it sanitize_input. and don't ever use stripslashes, its not a good fit for anything. if you need to html encode something, use htmlspecialchars (or htmlentities), if you need to encode it for SQL, use PDO::quote, if you need to encode it for shell arguments, use escapeshellarg. if you need to encode it for regex, use preg_quote, never stripslashes, the only thing it is good for, is corrupting your data.

the proper way to html encode arbitrary strings:

function hhb_tohtml(string $str): string {
    return htmlentities ( $str, ENT_QUOTES | ENT_HTML401 | ENT_SUBSTITUTE | ENT_DISALLOWED, 'UTF-8', true );
}

(optionally replace htmlentities with htmlspecialchars, but its the same input arguments)

and never do this:

action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>

if you want the POST to go to the current page, simply omit the action property completely. and most of the time, its best to let the browser decide encoding type itself (browsers will usually choose enctype="multipart/form-data" when files are bing updated, and application/x-www-urlencoded when no files are being uploaded, which is usually the smart thing to do, as multipart has a large metadata overhead, but 0 actual file content overhead, while urlencoded has a minimal metadata overhead, but a massive overhead for encoding binary data (up to 300% the original binary size), which files are usually made off of. let the browser choose itself, unless you have a very good reason not to.)

as for why you're getting NULLs, well look here

$stmt->bindParam(':geslacht', $geslacht);

you never define $geslacht, what you need is

$stmt->bindParam(':geslacht', $_POST['geslacht']);

maybe i missed something, but i'm out of time

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download