formatting string date into mysql date

Question

I've been reading threads here on how to format the string date probably, however it wont add the date. Instead it just adds 0000-00-00. How come it wont format the string date?

the $date variable could for instance be equal to 10/10/2014

    $date = str_replace('.', '-', $undate);
    $timestamp = strtotime($date);
    $newdate = date("Y-m-d", $timestamp);
    $wpdb->query("INSERT INTO " . $your_db_name . "
        (date, time, hometeam, awayteam, score)
         VALUES ($newdate, '$time', '$opp1', '$opp2', '$score')");

The problem you describe is a typical example of SQL injection that shows why creating SQL queries with string concatenation and variable interpolation has such a probelmatic history and present: http://bobby-tables.com/ — hakre, Jan 03 '15 at 16:05

Kevin · Accepted Answer · 2015-01-03T16:11:49.697

If you already got the correct date format Y-m-d, those needed to be quoted as well:

VALUES ('$newdate', '$time', '$opp1', '$opp2', '$score')

With @hakre's suggestion on the comment below. It also has a prepared statement wrapper, might as well use it for safer queries:

$prepared_statement = $wpdb->prepare("
    INSERT INTO $your_db_name
        (date, time, hometeam, awayteam, score)
        VALUES ( %s, %s, %s, %s, %s )
    ", 
    $newdate 
    $time, 
    $opp1,
    $opp2,
    $score
);

$wpdb->query($prepared_statement);

If this $your_db_name (most likely a table name) is a user input as well, you need to whitelist this as you cannot bind table names. A simple in_array() with predefined table names should suffice.

Psst, this is Wordpress: [WPDB - Protect Queries Against SQL Injection Attacks](http://codex.wordpress.org/Class_Reference/wpdb#Protect_Queries_Against_SQL_Injection_Attacks) — hakre, Jan 03 '15 at 16:06

formatting string date into mysql date

1 Answers1