MYSQL UPDATE injection without pipe

Question

I have a query like this:

update T_table set detail = 'XXXX' where num = 155;

which on my php file looks like this:

$sql = "update T_table set ".$_GET['field']." = '".$_GET['value']."' where num = ".$_GET['num'];
$output = mysql_query($sql);

I would like to know if it is possible to inject SQL where the XXXX are in the query. Because they will be replaced by a sting from $_GET, and if it is possible how would you do?

Important: My MYSQL database is not allowing double pipes (||) as a concatenation operator.

Sql injection by passing value like `B'and%20extractvalue(1,concat(0x0a,(select column_name from information_schema.columns where table_schema=database() and table_name='users' limit 1,1)));--%20` can be performed to fetch and alter details in the tables — Ankit Jindal, Jul 17 '20 at 17:29
**Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Jul 17 '20 at 19:26

score 1 · Answer 1 · answered Jul 17 '20 at 09:03

you should use PDO's prepared statements

$query = $db->prepare("update T_table set detail = :detail where num = :num;");
$query->bindParam(":detail", $_GET['detail']);
$query->bindParam(":num", $_GET['num']);
$query->execute();

if you need multiple fields this gets a little more complicated as the user's input can't really be trusted with arbitrary fields:

$allowedFields = ["detail", "cost", "name"];
$field = $_GET['field'];
if(in_array($field, $allowedFields) {
    $query = $db->prepare("update T_table set $field = :value where num = :num;");
    $query->bindParam(":value", $_GET['value']);
    $query->bindParam(":num", $_GET['num']);
    $query->execute();
}

MYSQL UPDATE injection without pipe

1 Answers1