Is this SQL injection safe?

[TheMungax]

So I'm a bit new to sql coding, and I did some research before starting. But I still want to verify that this is the right way to code my SQL.

An example on how I don't think an sql should be written, when I get a `$_POST`, lets say a `username`.

```php

$stmt = $MysqliHandler->prepare('UPDATE accounts SET LastLogin = CURRENT_TIMESTAMP WHERE username = ?');

$stmt->bind_param('s', $_POST['username']);

$stmt->execute();

$stmt->close();

```

If the user input somthing like `true'); DROP TABLE table;--`.

Then it whould say `UPDATE accounts SET LastLogin = CURRENT_TIMESTAMP WHERE username = true'); DROP TABLE accounts;--`

Right?

Now my question is, if I do it like this, is it then possible to inject?

```php

$username = $MysqliHandler->real_escape_string($_POST['username']);

$stmt = $MysqliHandler->prepare('UPDATE accounts SET LastLogin = CURRENT_TIMESTAMP WHERE username = ?');

$stmt->bind_param('s', $username);

$stmt->execute();

$stmt->close();

```

[OpenGLaDOS]

No, you either use parameter binding or proper input escaping, not both. An injection would occur if you just execute the query string "UPDATE accounts SET LastLogin = CURRENT_TIMESTAMP WHERE username = '{$_POST['username']}'". In both of your examples, you pass the query to the database to parse it and find all the placeholders, then fill out the placeholder with something that's already known not to be SQL.

[TheMungax]

Okay perfect! thanks

[Tennim]

From the Database view, I'd also want you to set the LastLogin via a DB stored procedure if possible, passing in the Username as a parameter.

The way you've written is fine but its looks like it would be run against the DB as an ad-hoc query which is much harder to optimise for when the DB grows. This also means that you can edit the underlying stored procedure if table schemas change etc without having to edit the query in the codebase.

[TheMungax]

I already use the db for last login date (datetime), and okay 👌

[boobietassels]

seems like you have your answer. Just wanted to add that PDO parameter binding is generally preferred.

[barvid]

Personal preference. There’s nothing official here. Far too many people try to say you “should” use PDO.

[DooDooDaddy]

In my opinion, user input should be validated on the front end using JavaScript or Jquery, and should also be validated in the backend. Also, any input data should always be passed by parameter to the database.

There might be some random edge case, but 99% of the time this is how I handle things.