SQL Query as a string - safe from injection?

Question

most of the time I've been doing SQL like this:

$pdo = new PDO($dsn, $usr, $pass);

$qry = 'SELECT * FROM `my_table` WHERE `pk_id` = '. $id .';';
$res = $pdo->query($qry);

but recently I've seen a few posts showing that the only way to be safe is using prepared statements - this isn't an issue really for me, and this probably has an answer, just one I couldn't find from Googling around.

surely, if all of my statements, end in .';' using concat is ok?

Thanks,

@baao the only argument I have in saying it isn't a duplicate is because I'm using concatenation and not passing variables inside the string or using `?` in places — treyBake, May 02 '18 at 12:22
What else than _passing variables inside the string_ is `pk_id = '. $id .';`? — baao, May 02 '18 at 12:23
The only definitive way of being safe is by using a prepared statement. Anything else is simply not professional and neglecting the security of your application. — Qirel, May 02 '18 at 12:23
If I can make an example , it would be that I can access your data just with `$id = " 00 OR 1 = 1"` which would result to `SELECT * FROM my_table WHERE pk_id = 00 OR 1 = 1 ;` — Fanie Void, May 02 '18 at 12:23
Concatenation and passing variables "inside" the string end up with exactly the same result — iainn, May 02 '18 at 12:23
@baoo more like the 3rd answer in your linked post which does actually answer my question, escaping it before use is apparently ok with concatenation — treyBake, May 02 '18 at 12:24
But why would you escape and concat it, instead of just using a prepared statement? I'd argue its easier to bind it than escaping it. — Qirel, May 02 '18 at 12:25
@Qirel more for curiosity rather than anything else :) I'm happy to switch over to start using prepared statements, it was just something that came to mind as I've been developing lately :) — treyBake, May 02 '18 at 12:26
If it's for educational purposes, let me give you an example. If you're escaping all your inputs, you might think of doing that with the password too before hashing it. That might change the hash and the password might be different. With a prepared statement you avoid that, as no data is altered in any way (escaping alters the data to make it 'safe' for insertion). A prepared statements sends two requests; one for the query, and one for the values - so no data is altered before being sent. — Qirel, May 02 '18 at 12:28
@Qirel ah yeah I'd much rather keep the data the same throughout haha was not aware escaping would cause that to happen! Definitely switching to prepared haha thank you :) — treyBake, May 02 '18 at 12:30
Password should not be altered, escaped or modified before hashing, though, just to have that said. But indeed, a prepared statement is superior in nearly every way, and is the better practice. — Qirel, May 02 '18 at 13:00

ThomasVdBerge · Accepted Answer · 2018-05-02T12:30:16.053

No.

In SQL, it does not give an error if you supply two semi colons at the end of your query.

So if a user could pass along this:

1; DROP TABLE users;

it will have the same consequences, with or without the semi colon in your code added at the end.

The huge benefit of prepared statements is that no data is being altered. It just simply sends two queries.

Here is a a nice source which contains a lot of SQL injection examples.

SQL Query as a string - safe from injection?

1 Answers1