Help with SQL Error in python

[Sharif]

 

I can't for the life of me to get this to work, so basically delete_entry is a entry box to where I can enter the unique ID of the row to delete it, and delete it. 

I can't seem to get that to simply take the data from it without throwing errors at me

[elpiop]

That's not a valid SQL statement. You need to concatenate delete_entry.get() with the rest of the statement ("DELETE from..." + delete_entry.get()). 

However, you should use a prepared statement instead to prevent against SQL injections as well

 

For example:

 

t = (delete_entry.get(),)
c.execute('DELETE FROM expense_sheet WHERE oid =?', t)

 

[Sharif]

59 minutes ago, elpiop said:

That's not a valid SQL statement. You need to concatenate delete_entry.get() with the rest of the statement ("DELETE from..." + delete_entry.get()). 

However, you should use a prepared statement instead to prevent against SQL injections as well

 

For example:

 

t = (delete_entry.get(),)
c.execute('DELETE FROM expense_sheet WHERE oid =?', t)

 

So in the real world, do you want to run some test on "t" before we pass it onto the place we need it?

 

As far as I know with my current limited knowledge, concatenation is combining of two or more things, cat + fish = catfish

How does concatenation work in this context? 

 

c.excecute('DELETE FROM expense_sheet WHERE oid = ' + delete_entry.get())

 

Also in your example, WHERE oid =?', t

is "?, t" like saying something or t's contents? (Also what is the ?) 

 

Sorry for all the questions

 

Edit

congrats on the 666 posts

[elpiop]

23 minutes ago, Sharif said:

So in the real world, do you want to run some test on "t" before we pass it onto the place we need it?

 

As far as I know with my current limited knowledge, concatenation is combining of two or more things, cat + fish = catfish

How does concatenation work in this context? 

 

c.excecute('DELETE FROM expense_sheet WHERE oid = ' + delete_entry.get())

 

Also in your example, WHERE oid =?', t

is "?, t" like saying something or t's contents? (Also what is the ?) 

 

Sorry for all the questions

 

Edit

congrats on the 666 posts

In your original statement "DELETE FROM expense_sheet where oid = delete_entry.get()", delete_entry.get() is not evaluated since it is inside of the string. So you would have to concatenate whatever delete_entry.get() evaluates to with the first part of the statement: "DELETE FROM expense_sheet WHERE oid = " + str(delete_entry.get()). 

 

However, like I mentioned, this is vulnerable to SQL injection. Since delete_entry comes from an input box, someone might enter "5; DROP TABLE expense_sheet;" In which case, the SQL statement becomes "DELETE FROM expense_sheet WHERE oid = 5; DROP TABLE expense_sheet;" There goes all your data. 

 

The ? represents where you want to substitute a value into the statement. You pass in values as a tuple (delete_entry.get(),) as the second argument to execute and you can pass in as many as you want (provided you have the same number of "?" placeholders in the statement). In this case, since it is a single value, you need the trailing comma to disambiguate between it being a tuple or an expression.

(delete_entry.get(),) This is a tuple
(delete_entry.get()) This is not

Basically instead of sending one SQL command to the server (being the pre formatted string you were originally intending to pass in), it will first send the SQL statement template (DELETE FROM expense_sheet WHERE oid =?) to the database and then it will send the parameters and evaluate the statement. As a result, the SQL injection does not occur, even with bad input like above. 

[Sharif]

On 7/14/2020 at 8:53 PM, elpiop said:

In your original statement "DELETE FROM expense_sheet where oid = delete_entry.get()", delete_entry.get() is not evaluated since it is inside of the string. So you would have to concatenate whatever delete_entry.get() evaluates to with the first part of the statement: "DELETE FROM expense_sheet WHERE oid = " + str(delete_entry.get()). 

 

However, like I mentioned, this is vulnerable to SQL injection. Since delete_entry comes from an input box, someone might enter "5; DROP TABLE expense_sheet;" In which case, the SQL statement becomes "DELETE FROM expense_sheet WHERE oid = 5; DROP TABLE expense_sheet;" There goes all your data. 

 

The ? represents where you want to substitute a value into the statement. You pass in values as a tuple (delete_entry.get(),) as the second argument to execute and you can pass in as many as you want (provided you have the same number of "?" placeholders in the statement). In this case, since it is a single value, you need the trailing comma to disambiguate between it being a tuple or an expression.

(delete_entry.get(),) This is a tuple
(delete_entry.get()) This is not

Basically instead of sending one SQL command to the server (being the pre formatted string you were originally intending to pass in), it will first send the SQL statement template (DELETE FROM expense_sheet WHERE oid =?) to the database and then it will send the parameters and evaluate the statement. As a result, the SQL injection does not occur, even with bad input like above. 

I read this a few a times to comprehend it, I didn't realise I was entering tuples the whole time as I was just following a guide, but customizing it to what I am trying to make.

So is the something.execute("fdgdfgdfgdgf") basically being sent as strings to sqlite3? Hence in order to run

"print("hello world")" would result in the code
where as print("hello world") would result in hello world, what we want

Am I comprehending this right? 

[elpiop]

2 hours ago, Sharif said:

I read this a few a times to comprehend it, I didn't realise I was entering tuples the whole time as I was just following a guide, but customizing it to what I am trying to make.

So is the something.execute("fdgdfgdfgdgf") basically being sent as strings to sqlite3? Hence in order to run

"print("hello world")" would result in the code
where as print("hello world") would result in hello world, what we want

Am I comprehending this right? 

 

Yes, something.execute("fdgdfgdfgdgf") just sends the string "fdgdfgdfgdgf" as a query to sqlite. 

 

You can see documentation for execute here: https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute

You can either pass in just a string as a query, or you can pass in the parameterized statement along with your tuple of values as a second argument.

 

Not sure I understand what you're asking, but

"print("hello world")" - This is just a string
print("hello world") - This prints "hello world"