Database Submit Entry Syntax Error

[Melford]

Hi, hopefully quick and simple; I'm currently trying to assign a preexisting button to an execute function which uploads data filled in via GUI to an already existing database however I'm receiving a syntax error in doing so, here's what I got hopefully not too bad!

Full Code:

from tkinter import *
import sqlite3

# Database Creation & Filename
conn = sqlite3.connect('Expense_Data.db')

# Create Cursor connecting to database
c = conn.cursor()

# Database Table Creation
'''
c.execute("""CREATE TABLE expenses   (
    name text,
    monthly_income integer,
    monthly_budget integer,
    monthly_cost_in_expenses integer
)""")
'''
def b3():
    c.execute("INSERT INTO Expense_Data.db VALUES :l1, :l2, :l3, :l4)"
                {
                'l1': l1.get(),
                'l2': l2.get(),
                'l3': l3.get(),
                'l4': l4.get()
                })


# Finalise || Commit Changes
conn.commit()

# Create Window Object
window = Tk()

# Define table contents (each row/column)
l1 = Label(window, text="Name")
l1.grid(row=0, column=0)

l2 = Label(window, text="Monthly Income")
l2.grid(row=1, column=0)

l3 = Label(window, text="Monthly Budget")
l3.grid(row=2, column=0)

l4 = Label(window, text="Monthly Expenses")
l4.grid(row=3, column=0)

# Define Entries
name_text = StringVar()
e1 = Entry(window, textvariable=name_text)
e1.grid(row=0, column=1)

Monthly_Income_text = StringVar()
e2 = Entry(window, textvariable=Monthly_Income_text)
e2.grid(row=1, column=1)

Monthly_Budget = StringVar()
e3 = Entry(window, textvariable=Monthly_Budget)
e3.grid(row=2, column=1)

Monthly_Expenses = StringVar()
e4 = Entry(window, textvariable=Monthly_Expenses)
e4.grid(row=3, column=1)

# Define ListBox
list1 = Listbox(window, height=6, width=35)  # check this
list1.grid(row=5, column=0, columnspan=2)

# Attach scrollbar to the list
sb1 = Scrollbar(window)
sb1.grid(row=2, column=2, rowspan=6)

list1.configure(yscrollcommand=sb1.set)
sb1.configure(command=list1.yview)

# Define buttons
b1 = Button(window, text="View All", width=12)
b1.grid(row=2, column=3)

b2 = Button(window, text="Search Entry", width=12)
b2.grid(row=3, column=3)

b3 = Button(window, text="Add Entry", width=12)
b3.grid(row=4, column=3)

b4 = Button(window, text="Update Selected", width=12, command=submit)
b4.grid(row=5, column=3)

b5 = Button(window, text="Delete Selected", width=12)
b5.grid(row=6, column=3)

b6 = Button(window, text="Close", width=12)
b6.grid(row=7, column=3)

window.mainloop()

Traceback error:

Error:
  File "C:/Users/jumbu/Desktop/python/test.py", line 21
    {
    ^
SyntaxError: invalid syntax

Location of Error:

def b3():
    c.execute("INSERT INTO Expense_Data.db VALUES :l1, :l2, :l3, :l4)"
                {
                'l1': l1.get(),
                'l2': l2.get(),
                'l3': l3.get(),
                'l4': l4.get()
                })

Feel free to take your time, any assistance will be greatly, greatly appreciated!!

[Melford]

UPDATE:
I found the syntax error and renamed def b3 to the following:

def submit():
    c.execute("INSERT INTO Expense_Data.db VALUES :l1, :l2, :l3, :l4")(
        {
            'l1': l1.get(),
            'l2': l2.get(),
            'l3': l3.get(),
            'l4': l4.get()
        })

However now the error seems to be with the SQLite table here is the error:

Error:
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\jumbu\AppData\Local\Programs\Python\Python37-32\lib\tkinter\__init__.py", line 1705, in __call__
    return self.func(*args)


  File "C:/Users/jumbu/Desktop/python/test.py", line 22, in submit
    c.execute("INSERT INTO Expense_Data.db VALUES :l1, :l2, :l3, :l4")(
sqlite3.OperationalError: near ":l1": syntax error

[Denni]

First you ought to look into using MVC methodology as your current means of handling your database connectivity is fraught with issues.

Next to handle your SQL simply do it this way then you can check your values before you use them and you can be sure that what you are doing is being done correctly by checking your SQL before you use it.
First this is the Syntax of your style of insert: INSERT INTO table_name VALUES (value1, value2, value3, ...)

def submit():
    Val1 = l1.get()
    Val2 = l2.get()
    Val3 = l3.get()
    Val4 = l4.get()
  # Debug purposes only print
    print('Values :',Val1, Val2, Val3, Val4)

    strSQL = ''
    strSQL += 'INSERT INTO Expense_Data.db '
    strSQL += 'VALUES (' + Val1
    strSQL += ', ' + Val2
    strSQL += ', ' + Val3
    strSQL += ', ' + Val4
    strSQL += ')'

  # Debug purposes only print
    print('SQL :',strSQL)
    try:
        c.execute(strSQL)
        c.commit()
    except:
        print('Insert Error Occurred')

So as can be seen with this you can easily add another Value to the insert if you wanted and the Insert is actually generic enough that you would design it to be fully generic and pass in the Values to insert and the Table to insert them into thus reducing your code base as well but this would only be really effective if you used the MVC methodology in its totality

[buran]

@Denni, concatenating variables coming from untrusted source/user input is prone to SQL injections.

[Denni]

@buran what I did was absolutely no different than what the OP did in their version all I did was separate it out -- further that is not how I would have handled it for my code and while yours is a good point to make you should have taken your comment further to explain to the OP what you are saying and how best to handle that --- which I will leave for you to do since you brought it up

Finally in my code base those "variables" never come from an untrusted source since in my MVC version all you get to do is pass the variables into a Stored Procedure that if available to outside sources (aka non-internally fully trusted ones) those variables would get validated prior to use in order to make sure the SQL query does not crash -- which in turn means injection attacks would get handled as well.

Also prior to allowing any data input fields to be used within code -- they always go through a validation routine in my code base -- to make sure that what got entered is valid data -- which can mean many different things for many different fields.

[buran]

c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", (l1.get(), l2.get(), l3.get(), l4.get()))

of course you can define the tuple (l1.get(), l2.get(), l3.get(), l4.get()) and assign it to name before that, on separate line, and use that name instead

payload = (l1.get(), l2.get(), l3.get(), l4.get())
c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", payload)

[buran]

@buran what I did was absolutely no different than what the OP did in their version all I did was separate it out

they don't know what they are doing, but clearly they try to implement it the right way, with placeholders, simply don't know how to do it.

which I will leave for you to do since you brought it up

I already have done that, simultaneously with your answer

Finally in my code base those "variables" never come from an untrusted source

We are not discussing your codebase, but OP snippet.

[Denni]

@buran that is true we were supposed to be discussing that code snippet and again this piece of code you give has exactly the same issue with injection attacks as the one I gave but yours does not allow for easy validation of the SQL statement prior to using it where mine does.
c.execute("INSERT INTO Expense_Data.db VALUES (?, ?, ?, ?)", (l1.get(), l2.get(), l3.get(), l4.get()))

Further I do mine the way I do based on how I have set up my MVC Database Class handler where the SQL statement to be used is a Class Variable that gets built by the stored procedure and the SELECT, INSERT, DELETE, UPDATE all get handled by 2 generic database calls

Okay now on to explaining what buran means by injection attacks since buran chose not to explain it at all...

Attackers can use SQL Injection vulnerabilities to bypass application security measures. They can go around authentication and authorization of a web page or web application and retrieve the content of the entire SQL database. They can also use SQL Injection to add, modify, and delete records in the database.

What are these vulnerabilities basically if coded properly and put into a variable that is getting concatenated to a SQL statement without prior validation one could input a SQL Query that did many things such as send the entire contents of you database to a remote location. Or completely delete permanently the entire contents of your database or insert a trigger that secretly sends all data activity to some remote location and the list goes on. So when receiving data from a user or any other external source is it extremely important to validate that data prior to allow it to be used. This of course should actually occur at the point of reception which makes those inline get calls (above) twice as dangerous since they leave no room for validation.

[Melford]

Thanks for the feedback @Denni, I had asked a question before as I'm trying to learn as much as possible from integrating a database via GUI for an assignment in like May or something, just want practice in for it so any assistance would help my understand a lot but also don't get mad cause I'm quite frankly awful hence why I'm cramming some practice in now. Now the disclaimers over with hopefully I can ask some dumb questions without upsetting anybody :D

Do I need to define the get attribute?

Error:
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\jumbu\AppData\Local\Programs\Python\Python37-32\lib\tkinter\__init__.py", line 1705, in __call__
    return self.func(*args)
  File "C:/Users/jumbu/Desktop/python/test.py", line 22, in submit
    val1 = l1.get()
AttributeError: 'Label' object has no attribute 'get'

[buran]

Sorry, I overlooked that. You should use .get() method with the StringVars, not the Labels