PDO INSERT not working anymore

WebMachine · November 28, 2014, 8:45pm

I have the following code:

   <?php
session_start();
require_once('inc/db_connect.php');

// initialize extra variables
$gender = '';
$wait_list = '';
$referral = '';

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

    $date_submitted = date('Y-m-d'); 
    $date_modified = '0000-00-00';
    // echo $date_submitted;

    //Profile Information
    $org_name = $_POST['org_name'];
    $org_type = $_POST['org_type'];
    $prog_name = $_POST['prog_name'];
    $hours = $_POST['hours'];
    $url = $_POST['url'];
    $address = $_POST['address'];
    $city = $_POST['city'];
    $province = $_POST['province'];
    $postal = $_POST['postal'];
    $main_phone = $_POST['main_phone'];
    $tollfree = $_POST['tollfree'];
    $contact_name = $_POST['contact_name'];
    $contact_position = $_POST['contact_position'];
    $directline = $_POST['directline'];
    $cell = $_POST['cell'];
    $email = $_POST['email'];
    
    //General Information
    $age = $_POST['age'];
    $gender = $_POST['gender'];
    $religion = $_POST['religion'];
    $disabilities = $_POST['disabilities'];
    $addictions = $_POST['addictions'];
    $min_require = $_POST['min_require'];
    $prereq = $_POST['prereq'];
    $wait_list = $_POST['wait_list'];
    $referral = $_POST['referral'];
    $usage_fee = $_POST['usage_fee'];
    $admission_fee = $_POST['admission_fee'];
    $languages = $_POST['languages'];
    $geo_area = $_POST['geo_area'];
    $volunteers = $_POST['volunteers'];
    $core_comp = $_POST['core_comp'];
    $other = $_POST['other'];
          
    if ($org_name == '') {
            header('location:form.php?msg=n');
            exit();
        }
        
    $data = array( 
        ':date_submitted' => $date_submitted,
        ':date_modified' => $date_modified,
        ':org_name' => $org_name, 
        ':org_type' => $org_type,
        ':prog_name' => $prog_name,
        ':hours' => $hours,
        ':url' => $url,
        ':address' => $address,
        ':city' => $city,
        ':province' => $province,
        ':postal'=> $postal,
        ':main_phone' => $main_phone,
        ':tollfree' => $tollfree,
        ':contact_name' => $contact_name,
        ':contact_position' => $contact_position,
        ':directline' => $directline,
        ':cell' => $cell,
        ':email' => $email,
        ':age' => $age,
        ':gender' => $gender,
        ':religion' => $religion,
        ':disabilities' => $disabilities,
        ':addictions' => $addictions,
        ':min_require' => $min_require,
        ':prereq' => $prereq,
        ':wait_list' => $wait_list,
        ':referral'=> $referral,
        ':usage_fee' => $usage_fee,
        ':admission_fee' => $admission_fee,
        ':languages' => $languages,
        ':geo_area' => $geo_area,
        ':volunteers' => $volunteers,
        ':core_comp' => $core_comp,
        ':other' => $other
    );
    
    echo '<pre>';
    print_r($data);
    echo '</pre>';

    // insert the data
        $query = "INSERT INTO organizations ( date_submitted, date_modified, org_name, org_type, prog_name, hours, url, address, city, province, postal, main_phone, tollfree, contact_name, contact_position, directline, cell, email, age, gender, religion, disabilities, addictions, min_require, prereq, wait_list, referral, usage_fee, admission_fee, languages, geo_area, volunteers, core_comp, other ) 
                        VALUES ( :date_submitted, :date_modified, :org_name, :org_type, :prog_name, :hours, :url, :address, :city, :province, :postal, :main_phone, :tollfree, :contact_name, :contact_position, :directline, :cell, :email, :age, :gender, :religion, :disabilities, :addictions, :min_require, :prereq, :wait_list, :referral, :usage_fee, :admission_fee, :languages, :geo_area, :volunteers, :core_comp, :other )
                        ";
        $stmt = $db->prepare($query);
        
        echo $query;
        // perform the database query
        $stmt->execute($data);
        
        echo "New form submission id: " . $db->lastInsertId();
        
        //header('location: form.php?msg=y');
        //exit;

I populated the database a few weeks ago with a form that uses this form_handler. It worked fine at the time.
Now it does not insert entries into the database, although a file used to update the database, using mostly the same code, does work.
My echo $query doesn’t tell me anything, because it just prints out the query with the placeholders.
My echo "New form submission id: " . $db->lastInsertId(); gives me a value of 0;
My print_r($data); is showing the correct entries that I want to insert into the database.

Can you see what went wrong with my code?

felgall · November 28, 2014, 9:05pm

For one thing all of the field validations are missing.

Simply assigning $_POST variables to other variables is a HUGE security hole as that then means you don’t know which variables are tainted and contain anything and which are untainted and can only contain data that is meaningful for the specific field.

So anything could be happening simply because a field contains a completely invalid value.

Best case is that your database could be filled with meaningless junk.

WebMachine · November 28, 2014, 9:11pm

I know. That part is not here, because it is not relevant to my problem. Please just tell me why it is not inserting the data into the database.

felgall · November 28, 2014, 9:20pm

But it is - you included the code that moves the garbage to any fields in the code you provided.

As to why the database call isn’t working, have you tested the value in $stmt after the prepare call so as to determine whether it is the prepare statement or the execute statement that is failing?

WebMachine · November 28, 2014, 9:45pm

But right now, I don’t care if it’s garbage.The public will not be able to access this yet. I do things one step at a time, and at this point, I only care about the INSERT working. I will deal with the garbage after.

have you tested the value in $stmt after the prepare call so as to
determine whether it is the prepare statement or the execute statement
that is failing?

Okay, thanks. That helps - I didn’t think to check those two statements.

Drummin · November 28, 2014, 10:37pm

But you should care if someone enters malicious code into your form to hack or delete your DB. At the very least quote and escape values by binding input.

$query = "INSERT INTO organizations (date_submitted,date_modified,org_name,org_type,prog_name,hours,url,address,city,province,postal,main_phone,tollfree,contact_name,contact_position,directline,cell,email,age,gender,religion,disabilities,addictions,min_require,prereq,wait_list,referral,usage_fee,admission_fee,languages,geo_area,volunteers,core_comp,other) 
                        VALUES ( :date_submitted, :date_modified, :org_name, :org_type, :prog_name, :hours, :url, :address, :city, :province, :postal, :main_phone, :tollfree, :contact_name, :contact_position, :directline, :cell, :email, :age, :gender, :religion, :disabilities, :addictions, :min_require, :prereq, :wait_list, :referral, :usage_fee, :admission_fee, :languages, :geo_area, :volunteers, :core_comp, :other )
                        ";
        $stmt = $db->prepare($query);
        $stmt->bindParam(':date_submitted', $date_submitted);
        $stmt->bindParam(':date_modified', $date_modified);
        $stmt->bindParam(':org_name', $org_name); 
        $stmt->bindParam(':org_type', $org_type);
        $stmt->bindParam(':prog_name', $prog_name);
        $stmt->bindParam(':hours', $hours);
        $stmt->bindParam(':url', $url);
        $stmt->bindParam(':address', $address);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':province', $province);
        $stmt->bindParam(':postal', $postal);
        $stmt->bindParam(':main_phone', $main_phone);
        $stmt->bindParam(':tollfree', $tollfree);
        $stmt->bindParam(':contact_name', $contact_name);
        $stmt->bindParam(':contact_position', $contact_position);
        $stmt->bindParam(':directline', $directline);
        $stmt->bindParam(':cell', $cell);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':age', $age);
        $stmt->bindParam(':gender', $gender);
        $stmt->bindParam(':religion', $religion);
        $stmt->bindParam(':disabilities', $disabilities);
        $stmt->bindParam(':addictions', $addictions);
        $stmt->bindParam(':min_require', $min_require);
        $stmt->bindParam(':prereq', $prereq);
        $stmt->bindParam(':wait_list', $wait_list);
        $stmt->bindParam(':referral', $referral);
        $stmt->bindParam(':usage_fee', $usage_fee);
        $stmt->bindParam(':admission_fee', $admission_fee);
        $stmt->bindParam(':languages', $languages);
        $stmt->bindParam(':geo_area', $geo_area);
        $stmt->bindParam(':volunteers', $volunteers);
        $stmt->bindParam(':core_comp', $core_comp);
        $stmt->bindParam(':other', $other);
        // perform the database query
        $stmt->execute();

felgall · November 28, 2014, 11:05pm

The code is already doing that. Anyway escaping has nothing to do with security - it is unnecessary in this situation as the code and data are being kept separate and escaping is only for converting valid data so it doesn’t get misread as code.

Drummin · November 28, 2014, 11:27pm

I was not aware that $stmt->execute($data); would bind the data. I thought it just processed input.

felgall · November 29, 2014, 2:07am

With PDO the execute includes the bind. With mysqli you need to bind the data before calling execute.

WebMachine · November 29, 2014, 2:44am

Well, I learned something there. I often wondered about the difference between those two methods.

So nothing glaring here that would cause the data to not be inserted?

Drummin · November 29, 2014, 2:49am

So values are being quoted and apostrophes escaped? ' etc?

felgall · November 29, 2014, 7:02am

That would be completely unnecessary since the data is not being jumbled with the code.

jemz · November 29, 2014, 9:48am

@WebMachine,

put your code inside the try catch.

salathe · November 29, 2014, 11:44am

The single quickest thing you can do at this stage is to change PDO’s error mode from the default (“silent”) to throwing exceptions. This will mean that if anything goes wrong for PDO, your script will halt immediately and shout about it.

The following line can be added to your script, or probably more helpfully to db_connect.php (somewhere after creating $db).

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

WebMachine · November 29, 2014, 1:46pm

Thanks for those suggestions. I won’t be able to get back to this until later this weekend, but I will try that and see what happens.

WebMachine · December 3, 2014, 8:54pm

I thought the code was working again, but the problem is still there. I tried what @salathe suggested, but no error messages showed up.

I tried putting my sql statement and the related PDO statements in a try … catch like @jemz suggested, but nothing happened.

I I tried printing out the value of $stmt after the prepare call as @felgall suggested, and I got

PDOStatement Object
(
    [queryString] => INSERT INTO organizations ( date_submitted, org_name, org_type, prog_name, hours, url, address, city, province, postal, main_phone, tollfree, contact_name, contact_position, directline, cell, email, age, gender, religion, disabilities, addictions, min_require, prereq, wait_list, referral, usage_fee, admission_fee, languages, geo_area, volunteers, core_comp, other ) 
						VALUES ( :date_submitted, :org_name, :org_type, :prog_name, :hours, :url, :address, :city, :province, :postal, :main_phone, :tollfree, :contact_name, :contact_position, :directline, :cell, :email, :age, :gender, :religion, :disabilities, :addictions, :min_require, :prereq, :wait_list, :referral, :usage_fee, :admission_fee, :languages, :geo_area, :volunteers, :core_comp, :other )
						
)

Drummin · December 3, 2014, 9:45pm

Any chance you tried the code I posted in post# 6?
The problem MAY be the space around field names,e.g.

 date_submitted, org_name,

Might need to use no spacing

date_submitted,org_name,

Not sure but hours MIGHT be a reserved word in which case you would need to add back tics around this field.

ahundiak · December 3, 2014, 10:18pm

Did you enable error processing as @salathe suggested in post #14? Are you reasonable sure your execute is being called?

ahundiak · December 3, 2014, 10:22pm

The problem MAY be the space around field names,e.g.

date_submitted, org_name,
Might need to use no spacing

date_submitted,org_name,
Not sure but hours MIGHT be a reserved word in which case you would need to add back tics around this field.

Come on folks. The isn’t stackoverflow.com! If you think a space might be the problem then test it instead of guessing.

Likewise, consider searching for “mysql reserved words” using your favorite search engine. You might be astonished to find a list of words that mysql reserves.

Drummin · December 3, 2014, 10:58pm

Hey thanks for the kind feedback. I suppose you would setup a database and tables to test this query?