Security Review

// SAFE: Parameterized query
String query = "SELECT * FROM users WHERE username = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, userInput);

# SAFE: Parameterized query
cursor.execute("SELECT * FROM users WHERE username = %s", (user_input,))

// SAFE: Parameterized query (node-postgres)
const result = await client.query('SELECT * FROM users WHERE id = $1', [userId]);

// SAFE: Stored procedure
CallableStatement cs = connection.prepareCall("{call sp_getUser(?)}");
cs.setString(1, username);

// SAFE: Allowlist for table names
switch(tableName) {
    case "users": return "users";
    case "orders": return "orders";
    default: throw new InputValidationException("Invalid table");
}

# VULNERABLE: String concatenation
query = "SELECT * FROM users WHERE name = '" + user_input + "'"

# VULNERABLE: f-string interpolation
query = f"SELECT * FROM users WHERE id = {user_id}"

# VULNERABLE: format() method
query = "SELECT * FROM users WHERE name = '{}'".format(user_input)

// VULNERABLE: Template literal
const query = `SELECT * FROM users WHERE id = ${userId}`;

// VULNERABLE: String concatenation
const query = "SELECT * FROM users WHERE name = '" + userName + "'";

# SAFE: ORM methods
User.objects.filter(username=user_input)

# VULNERABLE: raw() with interpolation
User.objects.raw(f"SELECT * FROM users WHERE name = '{user_input}'")

# VULNERABLE: extra() with unvalidated input
User.objects.extra(where=[f"name = '{user_input}'"])

# SAFE: ORM methods
session.query(User).filter(User.name == user_input)

# VULNERABLE: text() with interpolation
session.execute(text(f"SELECT * FROM users WHERE name = '{user_input}'"))

// VULNERABLE: User-controlled query operators
db.users.find({ username: req.body.username, password: req.body.password });
// Attack: { "username": "admin", "password": { "$gt": "" } }

// SAFE: Explicit type checking
const username = String(req.body.username);
const password = String(req.body.password);
db.users.find({ username: username, password: password });

# VULNERABLE: Shell command
os.system(f"mkdir {directory_name}")

# SAFE: Built-in function
os.makedirs(directory_name, exist_ok=True)

# VULNERABLE: Shell=True with user input
subprocess.run(f"convert {input_file} {output_file}", shell=True)

# SAFE: List of arguments, shell=False
subprocess.run(["convert", input_file, output_file], shell=False)

# Allowlist for permitted commands
ALLOWED_COMMANDS = {"convert", "resize", "rotate"}
if command not in ALLOWED_COMMANDS:
    raise ValueError("Invalid command")

# Validate arguments against safe patterns
if not re.match(r'^[a-zA-Z0-9_\-\.]+$', filename):
    raise ValueError("Invalid filename")

// SAFE: Escape special characters
String safeName = LdapEncoder.filterEncode(userName);
String filter = "(&(uid=" + safeName + ")(userPassword=" + safePassword + "))";

# VULNERABLE: User input in template
template = Template(f"Hello {user_input}")

# SAFE: Pass user input as variable
template = Template("Hello {{ name }}")
template.render(name=user_input)

// VULNERABLE: String concatenation
String query = "//users/user[name='" + userName + "']";

// SAFE: Use parameterized XPath
XPathExpression expr = xpath.compile("//users/user[name=$name]");
expr.setVariable("name", userName);

# SQL Injection
grep -rn "execute.*+" --include="*.py"
grep -rn "raw_sql\|rawQuery\|raw(" --include="*.py" --include="*.js"
grep -rn "\\.query\\(.*\\+" --include="*.js"
grep -rn "\\$.*\\+" --include="*.php"

# Command Injection
grep -rn "os\\.system\\|subprocess\\.run.*shell=True\\|os\\.popen" --include="*.py"
grep -rn "child_process\\.exec" --include="*.js"
grep -rn "system(\\|exec(\\|shell_exec(" --include="*.php"

# Template Injection
grep -rn "Template(.*\\+" --include="*.py"
grep -rn "render_template_string" --include="*.py"

# LDAP Injection
grep -rn "ldap_search\\|ldap_bind" --include="*.py" --include="*.php"