MFA with AuthPlus: Extracting TOTP Values and Generating QR Codes for SQLite DB

To extract TOTP values from an SQLite database and generate QR codes for AuthPlus, you must query the secret key column using standard SQL, then pass that raw base32 string to a Python library like qrcode or pyotp. This process involves reading the database schema, formatting the secret correctly, and rendering the URI into a visual image that users can scan with their authenticator apps.

Understanding the Database Schema

Before writing any extraction logic, it is crucial to inspect the structure of your SQLite database. The AuthPlus system likely stores multi-factor authentication secrets in a specific table, often named users, accounts, or mfa_secrets. The critical field is usually labeled totp_secret, secret_key, or authenticator_secret. This column typically contains a Base32 encoded string. If the data is stored as hexadecimal or plain text, conversion steps will be required before generating the QR code. You can inspect the schema by connecting to the database file using the sqlite3 command-line tool or a GUI client like DB Browser for SQLite. Run the following diagnostic command to view the table definitions:

sqlite3 authplus.db ".schema"

Look for the table containing user credentials. Note the exact column name for the secret. For example, if the table is users and the column is totp_secret, your SQL query will need to select this specific field. Do not assume generic names; always verify against your actual deployment.

Extracting Secrets via SQL Queries

Once the schema is confirmed, you can write a SQL query to retrieve the necessary data. It is best practice to limit the result set during development to avoid loading large datasets into memory. A typical query might look like this:

SELECT id, username, totp_secret FROM users WHERE mfa_enabled = 1 LIMIT 5;

This query assumes a boolean flag mfa_enabled exists to filter only active MFA users. Adjust the WHERE clause based on your specific business logic. If you are migrating data or setting up new accounts, you might need to insert secrets rather than extract them. However, for QR generation, extraction is the primary step. Ensure that the extracted secret is trimmed of whitespace. SQLite sometimes includes trailing spaces in text fields, which can cause decoding errors in subsequent libraries. Use the TRIM() function if necessary:

SELECT TRIM(totp_secret) FROM users WHERE id = 1;

The output should be a clean, uppercase Base32 string (e.g., JBSWY3DPEHPK3PXP). If the secret appears corrupted or contains invalid characters, check the insertion method used by AuthPlus to ensure proper encoding at the source.

Generating QR Codes with Python

With the secret extracted, the next step is to construct the OTPAuth URI and render it as a QR code. The standard format for these URIs is defined by the OATH Consortium. The URI structure is:

otpauth://totp/Issuer:User?secret=SECRET&issuer=ISSUER

Here, Issuer is the name displayed in the authenticator app (e.g., "AuthPlus"), and User is the username or email. You can use the pyotp library to handle URI construction and the qrcode library to generate the image. First, install the dependencies:

pip install pyotp qrcode[pil]

Then, implement the generation logic:

import pyotp
import qrcode

def generate_totp_qr(secret, issuer="AuthPlus", account_name="[email protected]"):
    # Construct the OTPAuth URI
    uri = pyotp.totp.TOTP(secret).provisioning_uri(
        name=account_name, 
        issuer_name=issuer
    )
    
    # Generate the QR code image
    qr = qrcode.QRCode(
        version=1,
        error_correction=qrcode.constants.ERROR_CORRECT_L,
        box_size=10,
        border=4,
    )
    qr.add_data(uri)
    qr.make_image(fill_color="black", back_color="white").save("qr_code.png")

# Usage example with extracted secret
extracted_secret = "JBSWY3DPEHPK3PXP"
generate_totp_qr(extracted_secret)

The pyotp library handles the escaping and formatting of the URI, ensuring compatibility with Google Authenticator, Microsoft Authenticator, and other standards-compliant apps. The resulting qr_code.png file can be displayed to the user or downloaded.

Handling Edge Cases and Security

When automating this process, consider several edge cases. First, ensure that the secret is never logged or exposed in error messages. Second, verify that the secret length is valid; most TOTP implementations require a minimum length of 20 bits, but Base32 strings are typically 16 or 32 characters long. If you encounter issues where the generated QR code does not scan, double-check the character case. Base32 is case-insensitive, but some strict parsers may prefer uppercase. Also, ensure that no extra newlines or spaces are appended to the secret when reading from the database. For production environments, consider implementing a rotation mechanism. If a user loses access to their device, they will need a new QR code. Your script should support regenerating the secret and updating the SQLite record accordingly. Always hash the secret if it is not already encrypted, depending on your compliance requirements.

Frequently Asked Questions

What if my secret is stored as hex instead of Base32?

You must decode the hexadecimal string into bytes first, then encode those bytes into Base32. Use Python's binascii.unhexlify() followed by base64.b32encode(). Most modern systems store secrets directly in Base32 to save space and simplify parsing.

Can I generate QR codes without Python?

Yes, you can use command-line tools like qrencode if you have the secret string. However, constructing the correct OTPAuth URI manually is error-prone. Python libraries like pyotp are recommended because they handle URI formatting and validation automatically.

How do I update the secret if a user needs to reset MFA?

Generate a new random secret using pyotp.random_base32(), update the SQLite record with the new value, and generate a new QR code. Inform the user that their old authenticator entry will stop working once the new one is scanned.

Why does the QR code fail to scan on iOS?

This is often due to incorrect URI formatting or special characters in the username. Ensure the issuer name and username contain only alphanumeric characters and hyphens. Avoid spaces or symbols unless properly URL-encoded.

Is it safe to store TOTP secrets in plaintext in SQLite?

While common for small-scale applications, storing secrets in plaintext is a security risk. Consider encrypting the column using SQLite’s built-in encryption extensions or handling decryption in your application layer before passing the secret to the QR generator.