247-3 Prepared Statements and SQL Injections Question

Hi I am on the Data Engineering path, on the Prepared Statements and SQL Injections mission.

On step three, the lesson tempted to use the function get_email(name) to retrieve the email and address of a user. The get_email(name) function takes a name as input and execute a query:

 "SELECT email FROM users WHERE name = '" + name + "';"

To get both email and address, it defines the name as

name = "Joseph Kirby' UNION SELECT address FROM users WHERE name = 'Joseph Kirby"

I cannot figure out how this works since although the above statement combined the name and address for Joseph Kirby, the return value for the get_email() function is email, not name, how was the address became part of the output of the function?

Thanks
Xuehong

Hey, Xuehong. To help answer your question, please answer mine.

Do you understand that the query below returns both the name and the address (in different rows)?

SELECT email FROM users WHERE name = 'Joseph Kirby'
UNION
SELECT address FROM users WHERE name = 'Joseph Kirby';

Thanks Bruno,

I actually thought that the email and address will be returned on the same row. But now that is clear, I still have some trouble with it. Let’s put
name = "Larry Cain' UNION SELECT address FROM users WHERE name = 'Larry Cain"

into query_string = "SELECT email FROM users WHERE name = '" + name + "';
then the query string becomes,

query_string = "SELECT email FROM users WHERE name = '" + "Larry Cain' UNION SELECT address FROM users WHERE name = 'Larry Cain" + "';

Will cur.execute() ignore " + " and " + " at the beginning and the end of the query string, so that the actual query string is,
query_string = "SELECT email FROM users WHERE name = 'Larry Cain' UNION SELECT address FROM users WHERE name = 'Larry Cain';

If that is the case, I can see that this query will return both the email and address for Larry Cain. But how " + " was recognized as being OK to ignore?

Best,
Xuehong

Let’s follow your footsteps.

So we do it:

>>> name = "Larry Cain' UNION SELECT address FROM users WHERE name = 'Larry Cain"

You forgot a double quote character at the end there, but let’s proceed.

>>> query_string = "SELECT email FROM users WHERE name = '" + name + "';"

(Double quote character missing at the end again. To continue answering your question…)

Yes, sort of, but not quite. When you run x = 5+2, you don’t get 5+2 assigned to x, but rather 5+2 is evaluated and the result of the evaluation is assigned to x.

It’s the same thing here, the result of evaluating "SELECT email FROM users WHERE name = '" + "Larry Cain' UNION SELECT address FROM users WHERE name = 'Larry Cain" + "';" is assigned to query_string:

>>> print(query_string)
SELECT email FROM users WHERE name = 'Larry Cain' UNION SELECT address FROM users WHERE name = 'Larry Cain';

And we conclude that there’s nothing for cur.execute to ignore, there’s a legal query to run.

Hope this helps.