Click here to Skip to main content
14,600,363 members
Rate this:
Please Sign up or sign in to vote.
#! /usr/bin/env python3
print('Content-type: text/html\n')

import MySQLdb, cgi

def results_table(records):
    html = """ <!doctype html>
    <html>
    <head><meta charset="utf-8">
    <link rel="stylesheet" href="http://cgi.soic.indiana.edu/~dpierz/i211.css">
    <title>Employee Add</title></head>
        <body>
            <h1>New Employee Added!</h1>
            <table border='1' width='100%'>
           <tr><th>Employee_ID</th><th>Email</th><th>FirstName</th><th>LastName</th><th>Phone</th><th>Admin</th><th>Active</th></tr>
            {content}
            </table>
            <p><a href="EmployeeAdd.html">Go Back</a></p>
        </body>
    </html>"""

    table = ""
    for row in records:
            table += "<tr>"
            for item in row:
                table += "<td  align='center'>"+str(item)+"</td>"
            table += "</tr>"
    print(html.format(content = table))

form = cgi.FieldStorage()

email = form.getfirst("email", "")
first_name = form.getfirst("first_name", "")
last_name = form.getfirst("last_name", "")
phone = form.getfirst("phone", "")
admin = form.getfirst("admin", "")
active = form.getfirst("active", "")
    



try:				#Always surround .execute with a try!
        SQL = "INSERT INTO Employee (Email, First_name, Last_name,  Phone, Admin, Active)"
        SQL += "VALUES('" + email + "','" + first_name + "','" + last_name + "','" + phone + "','" + admin + "','" + active + "',);"
        cursor.execute(SQL)
        db_con.commit()            

        SQL = "SELECT * FROM Employee; "
        cursor.execute(SQL)
        results = cursor.fetchall()
except Exception as e:		#Here we handle the error
        print('<p>Something went wrong with the SQL!</p>')
        print(SQL)
        print("\nError:", e)
else:				#This runs if there was no error
        results_table(results)


What I have tried:

Can't find syntax error anywhere in code
Posted
Updated 22-Jun-20 22:02pm
Comments
0x01AA 23-Jun-20 5:38am
   
At the end of the statement there is a comma too much.
This ...."','" + active + "',);"
should be ...."','" + active + "');"
Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
Can't find syntax error anywhere in code

Syntax error is in SQL command
I would start with:
SQL = "INSERT INTO Employee (Email, First_name, Last_name,  Phone, Admin, Active)"
'                                                                                ^ add a space here

Then, your SQL command is subject to injection.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
   
Comments
Maciej Los 23-Jun-20 3:43am
   
5ed!
Patrice T 23-Jun-20 4:09am
   
Thank you
0x01AA 23-Jun-20 5:11am
   
I don't know MariaDB but I think also MariaDB does not need a space there. It looks more that the end of the statement has a comma to much "',);"
Patrice T 23-Jun-20 6:29am
   
Exact
Rate this:
Please Sign up or sign in to vote.

Solution 2

As Patrice and 0x01AA have pointed out, you should watch for spaces and other separators. Your statement does not have a space between the parentheses VALUES, and then an empty value at the end of the statement
INSERT... , Active)VALUES(... '" + active + "',);"

No big deal, and also as Patrice pointed out; this query is vulnerable to SQL Injection. Here is a recent article on how to avoid this
https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/[^]
Python parameterized query and Prepared Statement to Insert data into MySQL table
Sometimes you need to insert a python variable as a column value in the insert query. For example, a user has filled an online form and clicked on submit. So you need to insert those values into a MySQL table you can do that using a parameterized query. First, you need to take user input into a variable and pass that variable to the INSERT query as a placeholder ( %s ).
   
v2
Comments
Maciej Los 23-Jun-20 3:43am
   
5ed!
0x01AA 23-Jun-20 5:11am
   
I don't know MariaDB but I think also MariaDB does not need a space there. It looks more that the end of the statement has a comma to much "',);"
MadMyche 23-Jun-20 6:43am
   
I think you caught something I missed. Updating
0x01AA 23-Jun-20 6:52am
   
"Your statement does not have a space between the parentheses VALUES": It does not need one ;)
INSERT INTO(<Field List>)VALUES(<Value List>)does work also in MariaDB. Tested here:
https://paiza.io/projects/cVqtF6OnXc4YFOrhHn5HJQ?language=mysql[^]
Rate this:
Please Sign up or sign in to vote.

Solution 3

Not an answer, but suggestion.

I'd suggest to use PyPika · PyPI[^].

Why?
Quote:
The motivation behind PyPika is to provide a simple interface for building SQL queries without limiting the flexibility of handwritten SQL. Designed with data analysis in mind, PyPika leverages the builder design pattern to construct queries to avoid messy string formatting and concatenation. It is also easily extended to take full advantage of specific features of SQL database vendors.



If you would like to know how to build parameterized queries with python, please read this: Python to MariaDB Connector | MariaDB[^]

Examples:
#select
cursor.execute("SELECT first_name,last_name FROM employees WHERE first_name=%s", (some_name,))
#insert
cursor.execute("INSERT INTO employees (first_name,last_name) VALUES (%s,%s)", (first_name, last_name))
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100