Is PHP DateTime Date Class a Date From SQL Injection

Question

I usually use prepared PDO statements like casting (int) or PDO :: quote () to prevent SQL injection. For this application, I need to change the date using PHP before adding it to the request. Do I need to take extra steps to prevent SQL injection, or am I sure? Thanks

$date = new DateTime($_GET['suspect_user_provided_date']); $date->add(new DateInterval('P1D')); $sql='SELECT * FROM table WHERE date<"'.$date->format('Ym-d').'"'; 

Answer 1

It doesn't matter if the DateTime object is safe or not. You should avoid the data that you pass into the request and not rely on the security of the provided library. If you change the implementation, you do not need to worry about whether the new implementation is safe or not. You must always run away. Otherwise, you try to answer - and remember - for each function - was it safe for SQL? for HTML? for CSV? for http / mail headers? for ... don't! The line of code sending the request knows nothing about the implementation of DateTime and if it is safe or not

Answer 2

The DateTime constructor will DateTime the argument and try to make it a date.

If it cannot return false (actually throws an exception from PHP 5.3).

We believe that you are safe, because SQL injection is trying to "trick" the SQL query string with quotes, for example, and new DateTime returns either a DateTime instance or false (throws an exception).
But you can still handle the error (from PHP 5.3)

 try { $date = new DateTime($_GET['suspect_user_provided_date']); } catch (Exception $e) { echo "Error"; exit; } $date->add(new DateInterval('P1D')); $sql='SELECT * FROM table WHERE date<"'.$date->format('Ym-d').'"'; 

Answer 3

Good question: is PHP date formatter safe from SQL injection?

I assume the starting point is the hard-coding format according to your example. A date format string allows you to include raw characters in a formatted date that may contain unsafe characters, so if you use a variable for a format string, then the answer is definitely “No”, it is unsafe.

If you use a hard-coded format, as in the example you specified, then this is a more complicated question, but it boils down to “Can the output of DateTime::format ever deviate from the desired format?”

The answer to this question is: “Yes, it can - it can print false if it fails. It will not destroy your SQL, but may give you unexpected results.

In theory, this should be as bad as it gets.

However, you must think about protection. Needless to say, a subtle error will not be detected in the DateTime class, which will cause it to output a poorly formatted date. Typically, this error is not considered a security issue; that would be just annoyance. Especially if it is difficult to reproduce under normal use. But combined with passing it directly to SQL, this can easily be a security issue.

The lesson is defensive programming: sanitize everything. Even if you are sure that it is safe. Do not assume that your language or framework is error free. Defensive programming means being protected at every level, so an unexpected error in the program or out of your control cannot leave your code open to attack.

Answer 4

The example you posted is safe. But why not use prepared statements? Like this:

  $pdo = new PDO($dsn, $user, $pass, $options = array ( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION )); $date = new DateTime($_GET['suspect_user_provided_date']); $date->add(new DateInterval('P1D')); $sql=' SELECT * FROM table WHERE date < :dt'; $stmt = $pdo->prepare($sql); $params = array ( 'dt' => $date->format('Ym-d') ); try { $res = $pdo->execute($params); } catch (PDOException $e) { echo $e->getMessage(); } 

Answer 5

When it comes to SQL, it is often better not to think about what would be safe and what would not; just process it with the prepared instructions:

 $stmt = $db->prepare('SELECT * FROM table WHERE `date` < :now'); $stmt->execute(array( ':now' => $date->format('Ym-d'), )); 

Answer 6

Depending on the format of the date output. If you want to change the format and place it to display some text (for example, the name of the month or day in the current locale), this can lead to unsafe (or at least unsuccessful) requests, as it may have some quotation marks. Or, for some reason, you can use quotation marks. So yes, do not hesitate and do not use pdo->quote() or even better prepared statements.

 // safe $date = new DateTime($_GET['suspect_user_provided_date']); $date->add(new DateInterval('P1D')); $sql='SELECT * FROM table WHERE date<"'.$date->format('Ym-d').'"'; // failing $date = new DateTime($_GET['suspect_user_provided_date']); $date->add(new DateInterval('P1D')); $sql='SELECT * FROM table WHERE date<"'.$date->format('\O\"\h\a\i \t\o\d\a\y \i\s Ym-D').'"'; // not failing, whatever format you are using, using pdo::quote $date = new DateTime($_GET['suspect_user_provided_date']); $date->add(new DateInterval('P1D')); $sql='SELECT * FROM table WHERE date<'.$dbh->quote($date->format('\O\"\h\a\i \t\o\d\a\y \i\s Ym-D')); // not failing, whatever format you are using, using prepared statments $stmt = $dbh->prepare('SELECT * FROM table WHERE date < :date'); $date = new DateTime($_GET['suspect_user_provided_date']); $date->add(new DateInterval('P1D')); $stmt->bindValue(':date', $date->format('\O\"\h\a\i \t\o\d\a\y \i\s Ym-D'), PDO::PARAM_STR); $stmt->execute();