0

I am setting up a mysql app. This is my getUsername method connects using standard mysqldb formatting. Does this mean it is a prepared statement? Also, is this code safe, or am I vulnerable to SQL injection?

def selectUser(userName):
    try:
        username = pickle.loads(base64.decode(userName))
    except:
        username = "admin"
    query = "SELECT name FROM users WHERE name = '%s'"
    conn = MySQLdb.connect('localhost', 'dbAdmin', 'lja8j30lJJal##', 'blog');
    with conn:
        c = conn.cursor()
        c.execute(query, (username,))
Casey
  • 444
  • 1
  • 7
  • 22
  • 1
    I believe it is a _parameterized_ statement, but not a _prepared_ statement. And yes, it is safe from SQL injection. – John Gordon Aug 25 '18 at 05:24
  • 1
    I'm not a 100% sure how this works in Python, but I suspect, that `name = '%s'` will end up in something like `name = ''John''` or `name = 'NULL'`, which presumably isn't what you intended and in the former case likely causes a syntax error on the SQL side. Usually there are no quotes around parameters, as they will be added by the library, if necessary. – sticky bit Aug 25 '18 at 05:45
  • My idea is an attack vector is made possible because the pickle.loads() will error out as the method is supposed to be load(). This way, it will always be the admin user. – Casey Aug 25 '18 at 19:47

1 Answers1

0

No - there is no way to make a prepared statement in MySQLdb. You won't find any mysql_stmt_init(), mysql_stmt_prepare() or mysql_stmt_execute() in the MySQL API binding in _mysql.c.

For whatever reason, the author of MySQLdb chose to simulate parameters instead of using real server-side prepared statements.

To protect against SQL injection, the MySQLdb package uses Python string-format syntax. It interpolates dynamic values into SQL queries and applies correct escaping, i.e. adding \ before quote characters to make sure dynamic values don't contain string delimiters.

See my answer to How do PyMySQL prevent user from sql injection attack? for a demonstration.

However, escaping doesn't help if you need to use dynamic values for numeric constants.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Sticky bit left a comment above regarding the query quotes. Is this an attack vector or is a malformed query not existent for mysqldb? – Casey Aug 25 '18 at 19:46
  • There is a risk of SQL injection vulnerability if quotes are not done correctly. See examples in the link I provided for another Stack Overflow answer. – Bill Karwin Aug 25 '18 at 20:14