Why is php pdo not inserting record?

[colap]

<?php

if (session_status() == PHP_SESSION_NONE) {
    session_start();
}
require_once 'functions.php';

$dbh = mysql_connection();

$allowedExts = array("gif", "jpeg", "jpg", "png");
$temp = explode(".", $_FILES["file"]["name"]);
$extension = end($temp);

if ( ( ($_FILES["file"]["type"] == "image/gif") ||
        ($_FILES["file"]["type"] == "image/jpeg") ||
        ($_FILES["file"]["type"] == "image/jpg") ||
        ($_FILES["file"]["type"] == "image/pjpeg") ||
        ($_FILES["file"]["type"] == "image/x-png") ||
        ($_FILES["file"]["type"] == "image/png") ) &&
        //($_FILES["file"]["size"] < 20000) &&
        in_array($extension, $allowedExts)) {
    
    if ($_FILES["file"]["error"] > 0) {
        echo "Return Code: " . $_FILES["file"]["error"] . "<br>";
    } else {
        echo "Upload: " . $_FILES["file"]["name"] . "<br>";
        echo "Type: " . $_FILES["file"]["type"] . "<br>";
        echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
        echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br>";

        if (file_exists("upload/" . $_FILES["file"]["name"])) {
            echo $_FILES["file"]["name"] . " already exists. ";
        } else {
            move_uploaded_file($_FILES["file"]["tmp_name"], "upload/" . $_FILES["file"]["name"]);
            echo "Stored in: " . "upload/" . $_FILES["file"]["name"];
            
            $objDateTime = new DateTime('NOW');
            $created = $objDateTime->format("Y-m-d H:i:s");
            $modified = $objDateTime->format("Y-m-d H:i:s");
            
            $title = $_FILES["file"]["name"];
            $photo_url = "upload/" . $_FILES["file"]["name"];
            $user_id = $_SESSION['id'];
            $username = $_SESSION['username'];
                        
            $sql = 'insert into p_photos(title,photo_url,user_id,username,created,modified) values(:title,:photo_url,:user_id,:username,:created,:modified)';

            $sth = $dbh->prepare($sql);

            $rt = $sth->execute(array(':title' => $title, ':photo_url' => $photo_url, ':user_id' => $user_id, ':username' => $username, ':created' => $created, ':modified' => $modified));
        }
    }
} else {
    echo "Invalid file";
}
?>

File is uploaded in upload folder correctly. What's wrong with prepare and execute statement? What can be the reason not inserting record? What's the way to debug php pdo query?

Edited June 1, 2014 by php-coder

[Jacques1]

The last database error can be fetched with errorInfo(). In fact, if you use the default PDO settings, then you must check every single statement for errors. Otherwise, the code will happily keep running, and you never know what happened (as you can see).

 

A much better solution is to turn on exceptions so that PDO automatically throws errors instead of waiting for you to manually check the return values. For example, a proper configuration might look like this:

$database = new PDO('mysql:host=localhost;dbname=YOUR_DB;charset=utf8', 'YOUR_USER', 'YOUR_PASSWORD', array(
    PDO::ATTR_EMULATE_PREPARES => false,                    // use actual prepared statements instead of client-side escaping
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,            // turn on exceptions
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // fetch associative arrays by default (optional)
));

Note that your upload logic has several other issues:

• Users may willingly or unwillingly overwrite existing files. The problem is that there's a gap between checking if the file exists and allowing the script to use the filename. What if another request has already used the same filename in the meantime? Let's say two requests both want to upload a file named “kitten.jpg”. If the file doesn't exist yet, they're both allowed to use that name. However, now one of the requests will overwrite the file of the other request.
• Enforcing unique filenames also creates a denial-of-service vulnerability: What if a malicious user floods the upload folder with common filenames so that legitimate users can no longer take them?
• You allow the user to have double extensions like “.php.jpg”. This is a serious security issue and can lead to execution of arbitrary scripts depending on your webserver configuration.

The solution is to not let the user choose the filename or the extension. Of course you can store this information in your database and display it to the user. But you must choose the actual filename and make sure it's unique. There are basically two ways to get unique names: You can either use the value from an AUTO_INCREMENT column. This leaks detailed information about the uploads of other users, which may or may not be a problem. Another option is to generate a sufficiently long random number:

function generate_random_bytes($length)
{
    $random_bytes = null;

    if (function_exists('mcrypt_create_iv'))
    {
        $random_bytes = mcrypt_create_iv($length, MCRYPT_DEV_URANDOM);
    }
    elseif (function_exists('openssl_random_pseudo_bytes'))
    {
        $random_bytes = openssl_random_pseudo_bytes($length);
    }
    else
    {
        // Suppress warnings, because /dev/urandom is an OS-specific device
        $random_bytes = @file_get_contents('/dev/urandom', false, null, -1, $length);
    }

    if ($random_bytes)
    {
        return $random_bytes;
    }
    else
    {
        trigger_error('Failed to generate random bytes.', E_USER_WARNING);
        return false;
    }
}

A good filename would be bin2hex(generate_random_bytes(16)). That's 16 random bytes encoded as a hexadecimal string.

 

Then attach a single permitted extension.

[colap]

<?php

if (session_status() == PHP_SESSION_NONE) {
    session_start();
}
require_once 'functions.php';

$dbh = mysql_connection();

$allowedExts = array("gif", "jpeg", "jpg", "png");
$temp = explode(".", $_FILES["file"]["name"]);
$extension = end($temp);

if (( ($_FILES["file"]["type"] == "image/gif") ||
        ($_FILES["file"]["type"] == "image/jpeg") ||
        ($_FILES["file"]["type"] == "image/jpg") ||
        ($_FILES["file"]["type"] == "image/pjpeg") ||
        ($_FILES["file"]["type"] == "image/x-png") ||
        ($_FILES["file"]["type"] == "image/png") ) &&
        //($_FILES["file"]["size"] < 20000) &&
        in_array($extension, $allowedExts)) {

    if ($_FILES["file"]["error"] > 0) {
        echo "Return Code: " . $_FILES["file"]["error"] . "<br>";
    } else {
        echo "Upload: " . $_FILES["file"]["name"] . "<br>";
        echo "Type: " . $_FILES["file"]["type"] . "<br>";
        echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
        echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br>";

        if (file_exists("upload/" . $_FILES["file"]["name"])) {
            echo $_FILES["file"]["name"] . " already exists. ";
        } else {
            move_uploaded_file($_FILES["file"]["tmp_name"], "upload/" . $_FILES["file"]["name"]);
            echo "Stored in: " . "upload/" . $_FILES["file"]["name"];

            $objDateTime = new DateTime('NOW');
            $created = $objDateTime->format("Y-m-d H:i:s");
            $modified = $objDateTime->format("Y-m-d H:i:s");

            $title = $_FILES["file"]["name"];
            $photo_url = "upload/" . $_FILES["file"]["name"];
            $user_id = $_SESSION['id'];
            $username = $_SESSION['username'];

            $sql = 'insert into p_photos(title,photo_url,user_id,username,created,modified) values(:title,:photo_url,:user_id,:username,:created,:modified)';
            $sth = $dbh->prepare($sql);

            $rt = $sth->execute(array(':title' => $title, ':photo_url' => $photo_url, ':user_id' => $user_id, ':username' => $username, ':created' => $created, ':modified' => $modified));
        }
    }
} else {
    echo "Invalid file";
}
?>

Image is uploaded nicely, but not inserting record into p_photos table, what can be the reason?

[mac_gyver]

you already have a thread for this, merging your two threads....

[Jacques1]

Image is uploaded nicely, but not inserting record into p_photos table, what can be the reason?

 

Why don't you read the friggin' replies?

 

What's the point of asking a question, ignoring the replies and coming back with the same question a month later? All that does is waste everybodys' time (yours included) and piss off the people trying to help you.

Edited June 30, 2014 by Jacques1