PHP MYSQL reusable delete query

Question

trying to make a delete function which will delete from any table using a field and value.

problem is its not working and im not getting any errors from it, I can add stuff to the database but not delete them.

if (isset($_REQUEST['delete']))
{
    $table = $_POST['table'];
    $field = $_POST['field'];
    $value = $_POST['value'];

    deleteFromTable($database, $table, $field, $value);
}

function deleteFromTable($pdo, $table, $field, $value)
{
    $stmt = $pdo->prepare('DELETE FROM :table WHERE :field = :value');

    $criteria = [
        'table' => $table,
        'field' => $field,
        'value' => $value
    ];

    $stmt->execute($criteria);
}

aynber · Answer 1 · 2016-01-21T18:41:08.290

4

Tables cannot be used as a parameter. While insecure, you're better off just placing it into the query:

$stmt = $pdo->prepare("DELETE FROM `$table` WHERE $field = :value");

or

$stmt = $pdo->prepare('DELETE FROM `'.$table.'` WHERE `'.$field.'` = :value');

You really should check to make sure that $table is an acceptable value before you proceed with the query.

You also need colons on your parameters, like so:

$criteria = [
    ':value' => $value
];

edited Jan 21 '16 at 18:41

answered Jan 21 '16 at 18:06

aynber

22,380
8
50
63

Having such a function is highly risky, since someone could wipe tables you don't want to. I must agree with the remark of checking that `$table` is an acceptable value, or assign the proper `DELETE` permissions on the tables – El Gucs Jan 21 '16 at 18:14
I changed the code to what you suggested and added a query to see if the table and column but it cant find the column....i even changed the $field variable to a string and copied the column name in and no luck. any suggestions – Andrew Dean Jan 21 '16 at 18:39
I edited my answer, since I'd forgotten that fields could not be parameters, either. Thinking about the rest of it. – aynber Jan 21 '16 at 18:42
You should also have a colon on the parameter (edited to show). Beyond that, check for errors when running the function. A try/catch block here might be useful, as well as turning on the mysql query log while you're testing it. – aynber Jan 21 '16 at 18:44

Don't Panic · Answer 2 · 2016-01-21T18:57:28.667

You cannot use parameters for table names, and you also cannot use parameters for column names. As it is currently written, you ARE generating a syntax error when you execute your prepared statement, whether or not you can see it. If you do this:

$stmt = $pdo->prepare("DELETE FROM `$table` WHERE :field = :value");

you will not generate a syntax error, but it will still not be doing what you think it is. It will not treat :field as a column identifier, but as a value! So if the user posts something like

$_POST = ['table' => 'table1','field' => 1, 'value' => 1]

then in effect, the query will be

DELETE FROM `table1` WHERE '1' = '1'

This will delete every row from your table, which I assume you would not want. The statement you need is

$stmt = $pdo->prepare("DELETE FROM `$table` WHERE `$field` = :value");

But concatenating user input into your query like this is obviously a dangerous thing to do.

If you want to safely create a query like this, you can use a whitelist approach, where you define which tables can be deleted from, and by which keys, and check your user input against that before running your delete. Here is a basic example:

$allowed_delete_keys = array(
    'table1' => array('columnA', 'columnB'),
    'table2' => array('columnX', 'columnY')
); 
if (!isset($allowed_delete_keys[$_POST['table']])) {
    echo "You can't delete from this table.";
    exit;
} else {
    if (!in_array($_POST['field'], $allowed_delete_keys[$_POST['table']])) {
        echo "You can't delete records based on this key";
        exit;
    } else {
        deleteFromTable($database, $table, $field, $value);
    }
}

i implemented your method using this as my query $stmt = $pdo->prepare("DELETE FROM '$table' WHERE '$field' = :value"); but its still not finding the column — Andrew Dean, Jan 21 '16 at 19:06
@AndrewDean You need to use backticks around `$table` and `$field`, not single quotes. Or better yet, if you have named your tables and columns reasonably, you don't need _anything_ around `$table` and `$field`. — Don't Panic, Jan 21 '16 at 19:20
See this Q&A for more info on this: http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks/11321508#11321508 — Don't Panic, Jan 21 '16 at 19:23

PHP MYSQL reusable delete query

2 Answers2