Security and PDO with PHP when dealing with databases. Am I understanding this corectly?

Question

I'm very new to PHP and programming in general. I've come across an article about security, although not an issue yet in my case, I'm sure it will come up in the future at some point.

The article in question was about database input.

I'm using PDO most of the time while dealing with databases, however I'm confused about some parts. Perhaps someone can shed some light on a few things.

As I understand it, prepared statements in PDO for example:

SELECT <column> FROM <table name> WHERE <something>

Doesn't get execute right away(well, obviously) but only when execute(); is called. And gets interpreted as is. So having something like

"SELECT <column> FROM <table name> WHERE" . <userinput> OR 1=1;

Lets say userinput is the username

And

<userinput> OR 1=1

is a user input variable via a form or whatever, will get interpreted exactly like that, meaning the username will be

userinput OR 1=1

And obviously no username OR 1=1 will exist in the database so an error will be returned.

That this mean that PDO is safe(a strong word, I know) from things like SQL injection? Or other 'hacking' methods?

What can I use/do to sanitize user input in general?

http://stackoverflow.com/questions/8263371/how-prepared-statements-can-protect-from-sql-injection-attacks/8265319#8265319 — Your Common Sense, Nov 17 '13 at 16:25

score 0 · Answer 1 · answered Nov 17 '13 at 17:12

Yes it is safe, you can look at it as sandbox, if you have SQL like SELECT FROM books, it guaranties that input from user will not get out of boundaries (like modifying sql query), so it is safe from 1st order injection, but not from 2nd.

What i mean? Well PDO PREPARED statements(because you can use pdo without preparing statements in php) guaranties that you sql query is safe, but it doesn't filters the actual value.

Consider example: suppose we get some value from the form, the value will be 1); DROP TABLE books and we will save it in our database using our prepared statement INSERT INTO books VALUES(<value1>, ...),so the query will be executed successfully, value 1); DROP TABLE books will be saved in our database, but evil code will no be executed, no drop value. But if you then use our stored value in standard query, not prepared one. You will get hurt. But if you everywhere use PDO prepared statement your are safe. But i advice to filter values anyway.

score -1 · Answer 2 · answered Nov 17 '13 at 16:08

Make use of Prepared Statements on PDO and you can stop worrying about SQL Injection.

I am sorry as this is the simplest answer i could give for this question.

Source

EDIT :

Found this answer on SO

Statement stmt = conn.prepareStatement("INSERT INTO student VALUES(?)");
stmt.setString(1, user);
stmt.execute();

If "user" came from user input and the user input was

Robert'); DROP TABLE students; --

Then in the first instance, you'd be hosed. In the second, you'd be safe and Little Bobby Tables would be registered for your school.

Yes sir, I'm using prepared statements of course. I was just wondering about what happens "behind the curtains" with prepared statements. Always good to know a bit more. — user2994883, Nov 17 '13 at 16:10

Security and PDO with PHP when dealing with databases. Am I understanding this corectly?

2 Answers2