Im new to PHP and i try to make a button for purging data on PHP older than 7 days

Question

My code doesn't work , i do some searching and try to wrap the code together

<?php 
$del = mysqli_query($con,"DELETE FROM tb_absen_mingguan WHERE tanggal=$_GET[tanggal] <= CURRENT_DATE - INTERVAL 7 DAY;");
if ($del) {
        echo " <script>
        alert('Data telah dihapus !');
        window.location='?page=rekap&act=mingguan';
        </script>"; 
}

    ?>

and got the error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

what should i do ?

i try to change the tanggal and stuff but it doesnt work , and im pretty confused where the error at

Welcome to Stack Overflow! Your script is vulnerable to [SQL Injection Attack](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even if [you are escaping variables, its not safe](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string%5D)! You should always use [prepared statements and parameterized queries](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either MYSQLI or PDO instead of concatenating variables into the query. — Barmar, Aug 01 '23 at 01:57
Please read [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and fix your code. — ADyson, Aug 01 '23 at 08:12

Paul T. · Answer 1 · 2023-08-01T02:14:55.210

No date needs to be passed to the query, the table's tanggal date value can simply be the reference.

Not sure what is trying to be achieved by that DELETE query in the WHERE clause, but what I believe what you are trying to do is:

DELETE FROM tb_absen_mingguan WHERE tanggal < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);

... and on the plus side, this query does not have any SQL injection issues.

Try this dbfiddle example

The before/after example using the query looks like this:

score 1 · Answer 2 · answered Aug 02 '23 at 10:42

Here's the updated and safer version of the code:

<?php
// Assuming you have established the database connection in $con

if (isset($_GET['tanggal'])) {
    // Sanitize the input to prevent SQL injection
    $tanggal = mysqli_real_escape_string($con, $_GET['tanggal']);

    // Construct the SQL query with proper date comparison
    $sql = "DELETE FROM tb_absen_mingguan WHERE tanggal <= (CURRENT_DATE - INTERVAL 7 DAY) AND tanggal = '$tanggal';";

    $del = mysqli_query($con, $sql);

    if ($del) {
        echo "<script>
            alert('Data telah dihapus!');
            window.location='?page=rekap&act=mingguan';
            </script>";
    } else {
        echo "<script>
            alert('Failed to delete data. Error: " . mysqli_error($con) . "');
            window.location='?page=rekap&act=mingguan';
            </script>";
    }
}
?>

In the updated code, I've made the following changes:

Sanitized the $_GET['tanggal'] input using mysqli_real_escape_string to prevent SQL injection attacks.
Changed the SQL query to use proper date comparison. We need to specify both conditions in the WHERE clause: tanggal <= (CURRENT_DATE - INTERVAL 7 DAY) to ensure that the "tanggal" is older than 7 days from the current date and tanggal = '$tanggal' to ensure that we are deleting records with a specific date.
Added an error handling message in case the deletion operation fails.

Always ensure you sanitize and validate user inputs before using them in SQL queries to prevent potential security vulnerabilities. Additionally, consider using prepared statements with parameter binding instead of manually escaping user inputs to further enhance security.

Note that the use of `mysqli_real_escape_string` is [not safe enough](https://stackoverflow.com/a/22305173/7644018), and SQL injection issue(s) can still occur. Parameterized queries are the much better/safer way to go. — Paul T., Aug 03 '23 at 01:06

Im new to PHP and i try to make a button for purging data on PHP older than 7 days

2 Answers2