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 · Accepted Answer · 2016-01-21 18:41:08Z

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, 2016 at 18:41

answered Jan 21, 2016 at 18:06

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

El Gucs Over a year ago

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

Andrew Dean Over a year ago

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

aynber Over a year ago

I edited my answer, since I'd forgotten that fields could not be parameters, either. Thinking about the rest of it.

aynber Over a year ago

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.

Don't Panic · Accepted Answer · 2016-01-21 18:57:28Z

1

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);
    }
}

edited Jan 21, 2016 at 18:57

answered Jan 21, 2016 at 18:47

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

3 Comments

Andrew Dean Over a year ago

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

Don't Panic Over a year ago

@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 Over a year ago

See this Q&A for more info on this: stackoverflow.com/questions/11321491/…

Collectives™ on Stack Overflow

PHP MYSQL reusable delete query

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related