1

The question

I will try to articulate this as clearly and best I can:

Suppose that I have a python program which connects to a database on a server. Next I ask you to consider that this python program grabs Mysql queries from a JSON file, python reads them, and then they are used to make the database do something. Is this secure? Why don't others do the same? Have I made some kind of grave error in doing this? Is this vulnerable to SQL injection or the like?

I apologise for asking what is more than one question. I suppose I really want to know if this is a good or a terrible idea.

Context

I have no experience with security and began using python for server side stuff a week ago. I'm mostly doing this to learn. I'm trying to put together a website that uses python and Mysql to handle the database. This means:

  • At some point this code will reach production.
  • Because: 1) I'm not too good with security and 2) People will actually be using this I'm very anxious about how secure the code might be.

My goal when beginning this code was that I wanted a very easy and efficient way of making secure queries to my MySql database. As a result, I kept all of my queries in a JSON file, which python then reads and returns into a function decorator that makes a connection to the database.

The code

# Imports 
import json 
import mysql.connector 

# Class which reads json files 
class json_read():
    def __init__(self,name):
        self.name = name  
    def json_caller(self):
            with open(self.name) as f:
                f = json.load(f)[0]
                return f

# Returns the file needed to connect to the database 
f = json_read("database_connect.json") 
config = f.json_caller()

# Function decorater used to initiate the connection to the database 
def mysql_connect(func):
    def wrapper(*args, **kwargs):
        try: 
            cnx = mysql.connector.connect(**config)
            cursor = cnx.cursor(prepared=True) 
            cursor.execute(*args, **kwargs)
            result = cursor.fetchall() 
            print("\nConnection is stable @ the function " + func.__name__)
            print(result)
        except:
            print("\nConnection failed @ the function " + func.__name__)
    return wrapper 

# Returns the line from the json file associated with the key
class query_dbh():
    f2 = json_read("queries.json")
    def __init__(self, index): 
        self.index = self.f2.json_caller()[index]

@mysql_connect
def output(*args, **kwargs):
    pass 

# Select_names is the key
output(query_dbh("Select_names").index) 

How the queries are stored:

[
{
    "Select_names" : "SELECT first,last FROM user",
    "Select_id" : "SELECT id FROM user"

}
]
  • It sounds like you've re-invented stored procedures. Google that and see if it's what you're trying to do. – Daisetsu Oct 6 '18 at 3:26
  • Absolutely! Thanks very much for the new terminology. Is the way I'm currently doing things legitimate in terms of security? – Ranch Mayonaise Oct 6 '18 at 3:34
  • It doesn’t help more than using real stored procedures or real named placeholder queries. It might even be less secure since you are re-implementing something known-good with a homegrown untested variant. – John Keates Oct 6 '18 at 14:40
1

What you want to do is used stored procedures. This is built into MySql itself. It will help prevent risks of SQL injection, and potentially speed up your application because the query won't need to be compiled and planned every time it's executed.

OWASP has a great resource about how to prevent SQL injection. Here's the relevant section to stored procedures listing their benefits and drawbacks. https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_2:_Stored_Procedures

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.