1

I'm currently working on a Flask restful API that searches for doctors within a certain distance based on area. I would like to know exactly what kind of SQL injection it's vulnerable to, since sqllite3 does not allow multiple commands in one statement. It has a couple of different tables associated with it but I've included the relevant ones and what the sqllite query looks like within the API code. The API itself is pretty large so you'll have to forgive me for not posting the whole thing.

from flask import Flask
from flask_restful import Api, Resource, reqparse
import sqlite3

app = Flask(__name__)
api = Api(app)

class fullname(Resource):
    def get(self, zipcode,distance,fname,lname):
        con = sqlite3.connect('DoctorZipLookup.db')
        with con:
            cur = con.cursor()
            cur.execute("SELECT Latitude, Longitude FROM Zipcodes WHERE Zip = " + zipcode)
            #does some math and finds zipcodes less than distance away
            cur.execute("SELECT * FROM Doctors WHERE Zip IN(SELECT Zip FROM closezipcodes) AND FirstName LIKE '" + fname + "%' AND LastName LIKE '" + lname + "%' ORDER BY Zip")
            cur.execute("SELECT * FROM Comments ORDER BY id")
            #put doctors and their comments in a JSON object and return it


api.add_resource(fullname, "/fullname/<string:zipcode>&<string:distance>&<string:fname>&<string:lname>")
app.run(debug=True)

I understand that queries written like this are vulnerable to SQL Injection, and I've been trying some examples just to get a better understanding of how it works. So far though I haven't gotten it to do much of anything. Most examples I've found online include a ; to execute a second statement and drop a table or spit a lot more information out. But sqlite3 won't allow you to execute 2 statements at once, is there a way to get around this and execute something else? Alternatively, what harm can actually be done by modifying a single statement that's formatted like this? So far all I have figured out is doing something like

import requests
zip = '27265 or Zip LIKE \'%\''
resp = requests.get('http://127.0.0.1:5002/fullname/' + zip + '&10&first&last')

which would cause the Zipcodes table to vomit out all of its content. What else can be done with the way this is set up?

Weston Sapusek
  • 95
  • 8
  • 2
    Why does it matter? You know this is insecure (you've demonstrated an example of it yourself). Let's not encourage people to post explanations of how to exploit these vulnerabilities. – pault Jun 10 '19 at 17:16
  • Possible duplicate of [What is SQL injection?](https://stackoverflow.com/questions/601300/what-is-sql-injection) – pault Jun 10 '19 at 17:17
  • It's a practice lesson. I'm trying to understand how breaking things like this actually works, and not having much luck with it. – Weston Sapusek Jun 10 '19 at 17:22
  • How about `zip = '27265; DROP TABLE Zipcodes; --'` (don't try this). **Edit**: Also don't name your variable `zip`, as that's the name of a builtin. – pault Jun 10 '19 at 17:28
  • That was one of the first things I tried, based on https://xkcd.com/327/ But sqlite3 only allows you to execute one statement at a time, which seems to render most forms of SQL injection useless. That's why I was curious if there are still ways to do it that I don't know about – Weston Sapusek Jun 10 '19 at 17:31
  • 1
    Any SELECT query can have `UNION SELECT...` appended. Therefore if you could manipulate the last part of your query above, you could run any other SELECT query that returns two columns. That way an attacker could read virtually any other data in the database, including metadata, user-specific private data, and so on. – Bill Karwin Jun 10 '19 at 20:57

1 Answers1

0

Your question seems to be more asking what's the risk of SQL Injection? I'd say the easiest way to understand the risk is to watch these two well done videos by Computerphile (video 1 video 2). There's lots of ways to sanitize your inputs before hitting the database with it.

Jake H.
  • 18
  • 4
  • that first video is unavailable. Also I'm trying to learn specifically about SQL injections in sqlite3, which does not allow the execution of multiple commands at once. – Weston Sapusek Jun 10 '19 at 17:39