SQL injection Whilst Using Binds/Prepared Statements?

Question

I know nothing about SQL injection apart from the process to block it.

I was wondering, if an attacker would modify my prepared statement from:

$DB = $Con->prepare("SELECT * FROM Test WHERE username=?");

$DB->bind_param('s',$Username);

$DB->execute();

And his statement he entered was:

x' DROP TABLE Test

How would the bind/prepared statement process this request?

Would it return an error or continue? as the bind_param links specific values to said SQL Statement?

Why not just try it yourself? – Kermit Jan 11 '13 at 02:12 — Kermit, Jan 11 '13 at 02:12
http://stackoverflow.com/q/8263371/285587 – Your Common Sense Jan 11 '13 at 08:06 — Your Common Sense, Jan 11 '13 at 08:06

score 1 · Answer 1 · answered Jan 11 '13 at 02:13

1

No, the database would simply look for a record that has a username of x' DROP TABLE Test so you would probably end up with an empty result set.

answered Jan 11 '13 at 02:13

jeroen

so prepared statements are 100% secure? – user1968541 Jan 11 '13 at 02:23
As far as the values of columns go, yes, but if you want to use for example a variable for a field name, prepared statements (or escaping) is of no use, you would have to use a white-list of allowed names. – jeroen Jan 11 '13 at 02:35

score 0 · Answer 2 · answered Jan 11 '13 at 02:11

0

When using bind_param, the values will be escaped for you. You should still validate the data to make sure it's correct, but it's safe from injection

answered Jan 11 '13 at 02:11

Gareth Parker

so prepared statements are 100% secure? – user1968541 Jan 11 '13 at 02:20
There's never 100% secure. Look into XSS, which isn't a database injection so it won't drop tables, but rather another kind of injection. Long story short, you won't have to escape your inputs, but you should still research what other security you should implement at different stages – Gareth Parker Jan 11 '13 at 02:21

score 0 · Answer 3 · answered Jan 11 '13 at 02:15

0

Once you prepare a statement, it is pre-compiled. So any parameters you bind to it are sent as raw data and in no way could modify the SQL statement.

Your example would work fine, it would select all rows with the username x' DROP TABLE Test.

answered Jan 11 '13 at 02:15

Supericy

so prepared statements are 100% secure? – user1968541 Jan 11 '13 at 02:19
In terms of SQL injections, yes, they are secure. As long as you are binding parameters and not concatenating them in with the query, you are safe. – Supericy Jan 11 '13 at 02:20

3 Answers3