247-3 Prepared Statements and SQL Injections Question

Q&A DQ Courses
#1

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

#2

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';
#3

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

#4

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.