Go语言简易授权系统问题：密钥验证后插入用户信息失败

Hey David, let's work through the issues with your authorization system code. The core problem right now is two-fold: your SQL syntax is off, and you're using unsafe string concatenation that opens you up to SQL injection attacks. Let's fix that step by step.

First, What's Wrong With Your Current Code? #

• SQL Injection Risk: Directly plugging reglicenceEntry.Text() into your SQL string means an attacker could craft a malicious licence key to alter your database (like deleting tables or stealing data). Never do this!
• Invalid SQL Syntax: Most databases (like MySQL, PostgreSQL) don't support that IF EXISTS ... THEN INSERT syntax directly in a single query the way you've written it. You need to structure this either as two separate safe queries, or a single atomic query.

Solution 1: Two-Step Safe Query (Clear & Easy to Debug) #

This approach first checks if the licence key exists, then inserts the user if it does. We'll use parameterized queries (the ? placeholders) to avoid injection risks.

import (
    "database/sql"
    "fmt"
    // Don't forget your database driver, e.g. MySQL
    _ "github.com/go-sql-driver/mysql"
)

// Assume you already have a valid *sql.DB connection set up
func registerUser(licenceKey, username, password string) error {
    // Step 1: Check if the licence key exists (efficiently)
    var licenceExists bool
    checkLicenceQuery := "SELECT EXISTS(SELECT 1 FROM login WHERE LK = ?)"
    err := db.QueryRow(checkLicenceQuery, licenceKey).Scan(&licenceExists)
    if err != nil {
        return fmt.Errorf("could not verify licence: %w", err)
    }

    if !licenceExists {
        return fmt.Errorf("invalid licence key provided")
    }

    // Step 2: Insert the user (use parameterized queries here too!)
    // IMPORTANT: Never store plaintext passwords! Hash them first (see note below)
    insertUserQuery := "INSERT INTO users (username, password) VALUES (?, ?)"
    _, err = db.Exec(insertUserQuery, username, password)
    if err != nil {
        return fmt.Errorf("could not create user: %w", err)
    }

    return nil
}

Solution 2: Single Atomic Query (Avoids Concurrency Issues) #

If you want to make this an atomic operation (so no chance of the licence key being deleted between your check and insert), use a single query that only inserts the user if the licence exists:

func registerUserAtomic(licenceKey, username, password string) error {
    insertQuery := `
        INSERT INTO users (username, password)
        SELECT ?, ?
        WHERE EXISTS(SELECT 1 FROM login WHERE LK = ?)
    `

    result, err := db.Exec(insertQuery, username, password, licenceKey)
    if err != nil {
        return fmt.Errorf("registration failed: %w", err)
    }

    // Check if any rows were inserted (tells us if the licence was valid)
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return fmt.Errorf("could not verify registration result: %w", err)
    }

    if rowsAffected == 0 {
        return fmt.Errorf("invalid licence key")
    }

    return nil
}

Critical Extra Tip: Secure Password Storage #

You must not store plaintext passwords in your database. Use a strong hashing library like bcrypt to hash passwords before inserting them:

import "golang.org/x/crypto/bcrypt"

// Hash a password before storing it
hashedPassword, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
if err != nil {
    return fmt.Errorf("could not hash password: %w", err)
}
// Use hashedPassword in your INSERT query instead of the plaintext password

内容的提问来源于stack exchange，提问作者David Vega