Finding Security Vulnerabilities

Tide is a business banking platform, and your team owns the payments API that small-business customers use to move money between accounts. A colleague opened a pull request adding a funds-transfer endpoint, and you have been asked to review it before it reaches production.

import hashlib
import logging

API_KEY = "sk_live_4f9a1c2b8e7d3f0a9c1b2e3d4f5a6b7c"

app_logger = logging.getLogger("tide.payments")


@app.route("/api/accounts/<account_id>/transfer", methods=["POST"])
def transfer_funds(account_id):
    data = request.get_json()
    recipient = data["recipient_sort_code"]
    amount = data["amount"]
    card_number = data.get("card_number")

    app_logger.info(f"Transfer request: account={account_id} card={card_number} amount={amount}")

    query = "SELECT balance FROM accounts WHERE id = '" + account_id + "'"
    result = db.execute(query)
    balance = result[0]["balance"]

    if amount > balance:
        return {"error": "insufficient funds"}, 400

    db.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s" % (amount, account_id))
    db.execute("INSERT INTO transfers (account_id, recipient, amount) VALUES (%s, %s, %s)",
               (account_id, recipient, amount))

    return {"status": "completed"}, 200


def hash_password(password):
    return hashlib.md5(password.encode()).hexdigest()

Can you identify the potential security vulnerabilities in this snippet? Explain each risk and what you would change to close it.