PHP中INSERT与CREATE SQL语句执行失败求助及语法疑问

Hey there! Let's work through your problems one by one—first the query error, then your table name question.

1. Why Your INSERT Query Is Failing #

The main culprit here is your e-mail column name. In MySQL, any column name with special characters (like hyphens) needs to be wrapped in **backticks ()**. Without them, MySQL will interpret e-mailase minus mail`, which is a total syntax error. That's exactly why you're seeing the "Error querying database" message.

On top of that, you're directly shoving user input variables into your SQL string—this is a massive SQL injection risk. Never do this! Instead, use prepared statements to safely pass variables to your queries.

Fixed INSERT Query (With Prepared Statements) #

// Use prepared statements to avoid SQL injection (the safe way!)
$stmt = mysqli_prepare($db, "INSERT INTO user (surname, name, `e-mail`, password) VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, "ssss", $text, $text2, $text3, $text4);
mysqli_stmt_execute($stmt);

// Check for specific errors instead of a generic message
if(mysqli_stmt_error($stmt)) {
    die('Error inserting user: ' . mysqli_stmt_error($stmt));
}
mysqli_stmt_close($stmt);

2. Using an Email as a Table Name: Is It Correct? #

Short answer: Your current syntax is wrong, and even if you fix it, this is a really bad practice. Let's break it down:

What's Wrong With Your Current CREATE TABLE Line? #

Email addresses have special characters like @ and ., which aren't allowed in unquoted SQL identifiers. To use an email as a table name, you must wrap it in backticks (for MySQL):

// Syntax is correct now, but still not recommended
$query2 = "CREATE TABLE `$text3` ( name VARCHAR(30) PRIMARY KEY, password VARCHAR(30))";

Without those backticks, MySQL will throw a syntax error because it can't parse the email as a valid table name.

Why You Shouldn't Use Emails as Table Names #

Even if you fix the syntax, this approach has major red flags:

• SQL Injection Risk: Even with backticks, if you don't sanitize the email input perfectly, an attacker could craft a malicious email to run arbitrary SQL.
• Naming Limits: MySQL caps table names at 64 characters. Long emails might exceed this and break your code.
• Maintenance Headaches: If a user changes their email later, you'll have to rename the table (messy) or leave orphaned tables hanging around.
• Bad Database Design: It's way better to use a unique user ID (like an auto-incrementing integer from your user table) to name user-specific tables, e.g., user_data_123 where 123 is the user's ID.

Better Alternative #

After inserting the user into the user table, grab their auto-increment ID, then create a table using that ID:

// Get the newly inserted user's unique ID
$user_id = mysqli_insert_id($db);

// Create a safe, predictable table name using the user ID
$safe_table_name = "user_data_" . intval($user_id);
$query2 = "CREATE TABLE `$safe_table_name` ( name VARCHAR(30) PRIMARY KEY, password VARCHAR(30))";
$result2 = mysqli_query($db, $query2) or die('Error creating table: ' . mysqli_error($db));

Final Quick Tips #

• Always use prepared statements for queries that include user input—this eliminates SQL injection risks.
• Avoid using user-provided values (like emails) as table/column names—stick to controlled, predictable identifiers instead.

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