Problem with my Insert (Sqllite)

Hi i have a problem with my insert, idk what to change. I appreciate every help :slight_smile:

skript9 = “”"CREATE TABLE IF NOT EXISTS Ticket (
Ticket ID INTEGER PRIMARY KEY,
Eingangsdatum DATE,
Problembeschreibung Text,
Gelöstdatum DATE,
Loesungsbeschreibung Text,
Bearbeiter_ID INTEGER,
Station_ID INTEGER,
Standardlösung_ID INTEGER

)"""

Insert32 = “”“INSERT INTO Ticket VALUES (1, “2024-31-05”, Blackscreen, “2024-01-06”, Neuer_Bildschrim, 1, 1, ,1 )”“”

You already know more than we do. Namely what the problem is.

1 Like

When posting code use the pre-formatted text feature which is the </> button in the edit menu.

There is no way we can tell you what to change, without knowing: what is supposed to happen when you try the code? What happens when you try it, and how is that different? And especially: where is the actual code that tries to do the insert? You have only shown us some Python code to create some strings that look like SQL code. That does not actually cause any SQL queries to run.

But before trying to fix these problems, please read the pinned thread and follow the guide to format the code properly. Otherwise, it’s hard to read, and the forum software will change some details (to make it “look nice”, but this is terrible for code).

Guys I have the solution and sorry for not being specific about the problem I will do better in the future. I appreciate ever help, thank you :slight_smile:

IIRC, the recommended way to construct SQL statements, including INSERT is:

mysql = "SELECT field1, field2 FROM table WHERE (field=%s)" % (myvalue)

And is there a specific reason you are using triple quotes? This should work.

Insert32 = 'INSERT INTO Ticket VALUES (1, “2024-31-05”, Blackscreen, “2024-01-06”, Neuer_Bildschrim, 1, 1, ,1 )'

If double quotes are inside your SQL statement, then using single quotes on the start and end of the INSERT string should work.

I also noticed your double quotes are not plain text, they are “fancy quotes”, as if you used a word processor to make it and it autocorrected those to fancy quotes. Never use a word processor to write code, use a good text editor.

  1. Notepad++. https://notepad-plus-plus.org/ It has a lot of add-ins.
  2. Or use the free Visual Studio Code, which can integrate with Python. This also has a lot of extensions to use many other environments like Javascript, Django, Azure, etc. Download Visual Studio Code - Mac, Linux, Windows
  3. PSPad. I no longer recommend PSPad because it keeps crashing on me for the previous 3 Windows operating systems, however from the forum this seems rare. Which is why they are still unable to fix it.

I would strongly NOT recommend this. It is a book example of a construction that is vulnerable to SQL Injection attack. Python packages that allow connecting to a SQL database provides a construction like:

sql_conn.execute("SELECT name WHERE id=?", (dataID,))

e.g. where you put your SQL instruction with ? marking insertion points, and provide data for those points outside of the SQL string. This way the SQL engine can insert the data securely and in type safe manner.

1 Like

So my SQL statement must actually be inside the .execute parameter? Not constructed as a string variable?

I would like to make sure I get this right.

Wait, if they reverse engineer the code or scan the .EXE file they could find that string where it’s constructed as a variable. Is that how it works?

Thank you.

There Passing parameters to SQL queries - psycopg 3.2.0.dev1 documentation is an explanation about it in Psycopg3 for Postgresql engine. I advise you to read it carefully or you may once meet the Bobby Tables’ Mom :wink:

I can’t tell you yes or no, because the terminology here doesn’t make any sense.

The parameter for your SQL statement (dataID) should be wrapped into a 1-tuple, which is then passed as a separate argument when you call execute. You need to call execute anyway in order to actually run the query. But the point is that execute will put the query together, not your own code’s string formatting. execute has all the logic needed to make sure it can create the SQL query safely.

No, that has nothing to do with it.

The point is that when you use ordinary string formatting code, like with % or .format or an f-string, it does not care, and has no way to know that the result is supposed to be an SQL query. It just performs an ordinary text substitution.

If the value being substituted in depends in any way, no matter how indirect, on input from the user, then this is a security risk. The user can input something that, when substituted in, creates an SQL query with completely different meaning.

An attacker doesn’t need to “find that string”. It’s only necessary to know that the code makes an SQL query that involves user input. From that point, there are a few standard tricks that can easily break most such “templates”, even without knowing what the exact query is.

For example, supposing your field has an integer type, the user could try input like 1); DROP TABLE table;--, and now the table is gone. Your text substitution created a query that looks like SELECT field1, field2 FROM table WHERE (field=1); DROP TABLE table;--), and the SQL engine dutifully did a normal SELECT, then dropped the table, then ignored a useless comment. This involves very little knowledge of the actual database structure, and the “payload” part could do all sorts of other malicious things. Once the user can figure out a valid way to “close off” the intended query, it’s an open invitation to inject any arbitrary SQL commands (and anything else remaining on the line can just be commented away).

As a side note, there’s another form of protection against the worst effects of this form of attack, and that’s disallowing multiple statements in a single query. A lot of database backends behave this way, with semicolon delimiters being used only at interactive prompts (and being permitted at the end of the statement ONLY, in other contexts), and I’m happy to see psycopg3 moving that direction, compared to psycopg2 which happily lets you stack queries and then gives you the result from the last one:

>>> import psycopg2
>>> db = psycopg2.connect()
>>> cur = db.cursor()
>>> cur.execute("select 1; select 2")
>>> cur.fetchall()
[(2,)]

SQL injection is still a serious problem, but at least we can protect against table droppage. It’s probably possible in some DBMSes to do a massive sweeping DELETE or something, but I wasn’t able to get Postgres to do that (ERROR: WITH clause containing a data-modifying statement must be at the top level). So moving forward, it should be that the worst someone can do is mess with the current query - which could still be incredibly serious (imagine a user name of ' or '1' = '1 in a login query), but not as devastating as DROP TABLE.

But, just don’t have SQL injection vectors in the first place.

1 Like