Does sanitizing input data prevent sql injection?

Question

Well, table and column names cannot be replaced by parameters in PDO. As a requirement, mentioning static column name is not possible here. So, I use santize. Is it enough to prevent sql injection like bind param method. What can be best method ?

$id = filter_var($_POST['id'], FILTER_SANITIZE_STRING);
$text = filter_var($_POST['text'], FILTER_SANITIZE_STRING);
$column_name = filter_var($_POST['column_name'], FILTER_SANITIZE_STRING);
$result = $con->query("UPDATE menu SET $column_name='$text' WHERE mid=$id") OR die($con->error);

" Is it enough to prevent sql injection like bind param method." — Not even slightly! — Quentin, Jul 23 '18 at 09:33
Bear in mind 2nd order injection, when the data, once stored in your db, may be harmful at a later stage. https://portswigger.net/kb/issues/00100210_sql-injection-second-order — Teson, Jul 23 '18 at 09:42

score 1 · Answer 1 · answered Jul 23 '18 at 09:36

You can sanitise a value for syntax, you cannot sanitise it for meaning. SQL-injection by its definition means that you make the query do something different than was intended. Here you are leaving your query wide open for anyone to substitute column names, which are essential to the query structure. By allowing users to change columns, you don't have control over what columns may get changed in the query, which is by definition a form of SQL injection.

You need a whitelist of column names users are allowed to change. If a user is allowed to change any column (even the primary id…!?), and you don't feel like keeping a list of column names in an array, then you can query your database for the names of columns in the targeted table and use that as whitelist.

Does sanitizing input data prevent sql injection?

1 Answers1