SQL参数化查询指南 SQL防注入最佳实践

import psycopg2

conn = psycopg2.connect(database="mydatabase", user="myuser", password="mypassword", host="localhost", port="5432")
cur = conn.cursor()

sql = "SELECT * FROM users WHERE username = %s AND password = %s"
params = ("' OR '1'='1", "password") # 故意构造的恶意输入，但不会被执行

cur.execute(sql, params)

rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

import java.sql.*;

public class Example {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/mydatabase";
        String user = "myuser";
        String password = "mypassword";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE username = ? AND password = ?")) {

            pstmt.setString(1, "' OR '1'='1"); // 故意构造的恶意输入
            pstmt.setString(2, "password");

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("username") + " " + rs.getString("password"));
            }

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        }
    }
}

import psycopg2

conn = psycopg2.connect(database="mydatabase", user="myuser", password="mypassword", host="localhost", port="5432")
cur = conn.cursor()

sql = "SELECT * FROM users WHERE 1=1"
params = []

if username:
    sql += " AND username = %s"
    params.append(username)

if email:
    sql += " AND email = %s"
    params.append(email)

cur.execute(sql, tuple(params))

rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()