0

I want to protect publicly editable variables from SQL injection.

Here's an example of code showing collected form variables to be updated. First idea was to protect the variables as below;

$val1 = $val2 = strtoupper;
$val1 = $val2 = strip_tags;
$val1 = $val2 = trim;
$val1 = $val2 = mysqli_real_escape_string;

The next idea is to protect the database query as below;

$update_customer = "update customer set val_1='$val1',val_2='$val2' where foo='$foo'"; //update values set

$update_cust = strtoupper($update_customer);
$update_cust = strip_tags($update_customer);
$update_cust = trim($update_customer);
$update_cust = mysqli_real_escape_string($con, $update_customer);
$update_cust = mysqli_query($con, $update_customer); //inititate query

I presume using both ideas wouldn't be necessary but I'm not 100% on which I should use.

Peter Featherstone
  • 7,835
  • 4
  • 32
  • 64
  • 2
    Why not just use parameterized queries? - http://php.net/manual/en/mysqli.prepare.php – Peter Featherstone Aug 04 '17 at 19:37
  • Would doing that negate the requirement for these steps or would they be additional? –  Aug 04 '17 at 19:38
  • 1
    Negate pretty much, unless you want your variables to be trimmed and uppered and stripped of HTML tags for any other reason – Peter Featherstone Aug 04 '17 at 19:39
  • Okay I take the hint. This is for a basket page so it must be as protected as possible. I'll make some changes and come back. Thanks. –  Aug 04 '17 at 19:41
  • [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) – deceze Aug 04 '17 at 19:43
  • 1
    If you need help learning how to use [PDO](http://php.net/manual/en/book.pdo.php) for safe and secure queries, then you can check out [this answer that I wrote](https://stackoverflow.com/a/45514591/5827005) that demonstrates a function that I wrote that makes [Prepared Statements](https://www.w3schools.com/php/php_mysql_prepared_statements.asp) **easy**, **clean**, and **secure**. – GrumpyCrouton Aug 04 '17 at 20:05

2 Answers2

2

Seriously:

Stop trying to use low-pollution diesel cars and switch to solar powered vehices.

Aka, use Parameterized Queries using either PDO or MySQLi PHP database functionality.

The above link to Parameterised queries would be a replacement for your code as show, you would NOT use mysqli_real_escape_string, or need to strip_tags unless for your own personal preference for what sort of data you want to store in the database.

Martin
  • 22,212
  • 11
  • 70
  • 132
0

Forget all of what you are doing and look at parameterized queries using mysqli.

All you need to do with your code above is something like the below:

$stmt = mysqli_prepare($con, "UPDATE customer SET val_1=?,val_2=? WHERE foo=?");

mysqli_stmt_bind_param($stmt, "sss", $val1, $val2, $val3);
mysqli_stmt_execute($stmt);

The only reason you will need to do your trim(), strip_tags() or strtoupper() now is purely for personal preference and you can forget mysqli_real_escape_string() even exists.

Peter Featherstone
  • 7,835
  • 4
  • 32
  • 64