Click here to Skip to main content
15,860,861 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
#! /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
[no name] 23-Jun-20 5:38am    
At the end of the statement there is a comma too much.
This ...."','" + active + "',);"
should be ...."','" + active + "');"

Quote:
Can't find syntax error anywhere in code

Syntax error is in SQL command
I would start with:
VB
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[^]
 
Share this answer
 
Comments
Maciej Los 23-Jun-20 3:43am    
5ed!
Patrice T 23-Jun-20 4:09am    
Thank you
[no name] 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
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 ).
 
Share this answer
 
v2
Comments
Maciej Los 23-Jun-20 3:43am    
5ed!
[no name] 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
[no name] 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[^]
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:
Python
#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))
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900