SELECT within SELECT PDO prepared statement

2.6k views Asked by At

I'm thinking of an example like this one:

The request hits a page at an url (by whichever means), with a parameter. example.com/api/page?name=bob. It's my understanding that you should do a prepared statement to get the $_POST[name]parameter and make sure it's not anything funky, but it does so by evaluating the expression.

My first question is: How does it do so (evaluate the expression)?

My second question is: What if a user inputs something along the lines of "SELECT * FROM users" or "DROP TABLE users" that gets passed to the $_POST['name'] parameter (the ?name=bob at the end), what would happen in that case?

Just as an example, the final query would be something like

SELECT name, continent FROM world
WHERE continent IN
(SELECT continent FROM world WHERE name='Brazil')

the second select acting as the user input parameter - so $_POST['name'] contains this query SELECT continent FROM world WHERE name='Brazil'

Lastly, the third question i have is how can i protect against something like that?

I'm guessing PDO is design specifically to prevent query within query(?), but after reading around a bit i'm still quite confused.

I'm still learning all of this so if i'm not clear or specific enough in my inquiry please let me know, and i'll try and address that.

EDIT: To clear any confusion, what i'm doing is this:

$pdo = new PDO('..');
$sql = 'SELECT id FROM users WHERE username = :username';
$statement = $pdo->prepare($sql);
$statement->bindParam(':username', $_POST['username']);

Question is, what if $_POST['username'] contains 'SELECT * FROM users' (or any other query) ? How does prepare() work? Can what i described actually present a security hole? I need some help in understanding this.

1

There are 1 answers

1
notlisted On BEST ANSWER

To clear any confusion, what i'm doing is this:

$pdo = new PDO('..'); $sql = 'SELECT id FROM users WHERE username = :username'; $statement = $pdo->prepare($sql); $statement->bindParam(':username', $_POST['username']);

Question is, what if $_POST['username'] contains 'SELECT * FROM users' (or any other query) ?

This query would return the ids of all users with the username "SELECT * FROM users".

By passing $_POST['username'] as parameter the database knows that whatever string $_POST['username'] may contain it is NOT part of the query. It's just a string.

This prevents SQL injection since the parameter will NOT be executed. This also means that

SELECT name, continent FROM world WHERE continent IN (SELECT continent FROM world WHERE name='Brazil')

the second select acting as the user input parameter - so $_POST['name'] contains this query SELECT continent FROM world WHERE name='Brazil'

won't work. Because you can't include queries in parameters. Well you can but they will not be executed.