PHP环境下无预编译语句时如何防止SQL注入？

Alright, let's dive into practical, framework-compatible ways to sanitize user comments before storing them in your MySQLi database—since you can't use prepared statements or switch frameworks right now. These methods focus on PHP-side processing to neutralize injection risks:

1. Use MySQLi's Native Escape Function (Most Reliable) #

The mysqli_real_escape_string() function is tailored to your database connection's character set, making it far more reliable than handwritten regex for escaping SQL-specific characters. Critical note: You must establish your MySQLi connection first before calling this function—otherwise, it won't use the correct character set.

// Assume $mysqli is your established MySQLi connection
$raw_comment = $_POST['user_comment'];

// Escape special characters (single quotes, double quotes, backslashes, NULL, etc.)
$safe_comment = mysqli_real_escape_string($mysqli, $raw_comment);

// $safe_comment is now ready to be inserted into your database

This function targets exactly the characters MySQL interprets as syntax markers, so it avoids over-sanitizing valid user input while blocking injection attempts.

2. Sanitize Input with PHP's Filter Functions #

If your comments are strictly plain text (no HTML allowed, as you noted), combine escaping with filter_var() to strip unwanted markup and extra characters first:

$raw_comment = $_POST['user_comment'];

// Strip HTML tags and sanitize general input
$sanitized_comment = filter_var($raw_comment, FILTER_SANITIZE_STRING);

// Escape for MySQLi
$safe_comment = mysqli_real_escape_string($mysqli, $sanitized_comment);

This adds an extra layer: it removes any accidental or malicious HTML (which you already want to block) before handling SQL-specific risks.

3. Targeted Keyword Filtering (Use With Caution) #

If you need to block explicit SQL injection keywords (e.g., UNION, DROP), you can use a regex to remove or neutralize them. Be aware: This can accidentally remove valid user input (e.g., a comment mentioning "union negotiations"), so only use this if your use case allows for strict content restrictions.

$raw_comment = $_POST['user_comment'];

// Regex to match common SQL injection keywords (case-insensitive)
$sql_injection_pattern = '/\b(UNION|SELECT|DELETE|UPDATE|DROP|ALTER|EXEC)\b/i';

// Replace matched keywords with an empty string (or a placeholder like "[filtered]")
$filtered_comment = preg_replace($sql_injection_pattern, '', $raw_comment);

// Escape for MySQLi
$safe_comment = mysqli_real_escape_string($mysqli, $filtered_comment);

4. Enforce Input Length Limits #

Truncating overly long comments reduces the risk of complex injection payloads that rely on lengthy strings. Set a reasonable maximum length based on your use case:

$raw_comment = $_POST['user_comment'];

// Limit comment to 500 characters (adjust as needed)
$trimmed_comment = substr($raw_comment, 0, 500);

// Escape for MySQLi
$safe_comment = mysqli_real_escape_string($mysqli, $trimmed_comment);

Best Practice Summary #

• Prioritize mysqli_real_escape_string(): It’s the most robust method because it’s designed specifically for MySQLi and your connection’s character set.
• Combine layers: Use input sanitization (filter functions) + escaping + length limits for multiple lines of defense.
• Always connect first: Never call mysqli_real_escape_string() before establishing your database connection—character set mismatches will break its effectiveness.

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