Cronless records older than 1 week with PHP

[Evil_genius82]

Hi all,

I plan on deleting records which are older than one week from my MySQL database.I do not have access to cron on my current server so I plan on using cronless.com to schedule a request on the following php script.

<?php
$cron_id = $_GET['comm'];
if($cron_id == 20){
    require_once ('db_connect.php');    
    $stmt = mysqli_prepare($conn, "DELETE FROM dt_table WHERE CreatedOn < DATE_SUB(NOW(), INTERVAL 1 WEEK");
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);

    echo"success";
 }
 else{
     echo"fail";
     }

?>

Basically, the script above is working fine, but from my knowledge it is not safe as I am not preparing the statement correctly using variables. Whenever I tried the following, statement below, it deleted all the records

<?php
    $cron_id = $_GET['comm'];
    if($cron_id == 20){
        require_once ('db_connect.php'); 

        $q =  "DATE_SUB(NOW(), INTERVAL 1 WEEK)";

        $stmt = mysqli_prepare($conn, "DELETE FROM dt_table WHERE CreatedOn < ?");
        mysqli_stmt_bind_param($stmt, "s", $q);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_close($stmt);

        echo"success";
     }
     else{
         echo"fail";
         }

    ?>

Can anyone let me know what im doing wrong?

Thanks you for your time.

Ryan

[veedeoo]

Your first attempt was fine for me.

Why is it fine for me?
The $cron_id has nothing to do and it is not part of the query. So, whatever the value of the cron_id, it will not affect nor change the delete query. It may change the frequency of executing the query, but it has no controll on what to delete.

The query that will definitely require a bind_param is something like the scenario on my example below.

For example, we have a user interface that allow user to select and delete their personal files.

Based on the requirement of the interface, there will be user input involvment here. Let say the user click on the link below

<a href="delete.php?id=102"/> delete this item </a>

the delete.php can be coded like this

<?php

    if(isset($_GET)&&(filter_var($_GET['id'],FILTER_VALIDATE_INT))){

        $file_id =  filter_var($_GET['id'],FILTER_SANITIZE_NUMBER_INT);

        ## the bind parameter
        $stmt = $mysqli->prepare("DELETE FROM user_files WHERE file_id = ?");
        $stmt->bind_param('x', $file_id);
        $stmt->execute();
        $stmt->close();

      }

[diafol]

PS if you use crons of any description, make sure you log all events/errors.

[Evil_genius82]

Thank you for clearing this issue up. I understand now that since the post segment and the prepared statment are seperete, no injections can be performed. Its always safer to ask pros when in doubt. :)

Thanks all.