0

I've got a php script that adds a row into a db table with a DATE formatted column, e.g. 2016-11-02.

My query adds data to the table like so:

$duser = $_POST['username'];
$tdate = date("Y-m-d");

//build query
$query = " 
        INSERT INTO emodata (
            username,
            date
        ) VALUES (
            ':duser',
            ':tdate'
        ) 
    "; 

    // create tokens 
    $query_params = array( 
        ':duser' => $duser,
        ':tdate' => $tdate
    ); 

    try
    { 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($query_params); 
    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query - $tdate : " . $ex->getMessage()); 
    }

For some reason mySQL doesn't seem to think the format is correct. The error I receive is:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: ':tdate' for column 'date' at row 1

I've checked the php date format and it does produce YYYY-MM-DD format, which is the correct format for mysql DATE...

Do you guys think this might be to do with the prepared statement and parameter substitution? If so, is there a way round this or do I have to use some other form of secure query?

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Vaudeville
  • 1
  • 1
  • 1
    Don't quote the parameters in the SQL statement. Try `VALUES (:duser, :tdate)` instead. – Phylogenesis Aug 21 '17 at 14:25
  • Good news is your environment is set up correctly because you caught those errors. Spend a little time reading up on how PDO works, [the manual is quite thorough](http://php.net/manual/en/book.pdo.php) and you'll avoid simple mistakes like this. – tadman Aug 21 '17 at 15:29
  • Thanks that was it - I've always used quotes inside the parameters without issues, so never picked it up until it caused problems! – Vaudeville Aug 21 '17 at 17:13

0 Answers0