Company name registration form

Question

For reference only, new to coding, self-study, relax...

I have a registration form that creates a new SQL table. The company name they enter will be the name of the new table created each time they log in.

Everything is working fine except for the vulnerability in the table name variable.

I'm using PHP and MySQL.

I initially had issues if the company name had a space between the 2 words, but I fixed that using str_replace

I did a lot of testing and found that if I put the company name in "out" it breaks the code.

I received this error message:

"Fatal error: Uncaught mysqli_sql_exception: There is an error in your SQL syntax; check the manual for your MySQL server version for the correct syntax to use near 'out (id INT UNSIGNED AUTO_INCRMENT PRIMARY KEY, first_name VARCHAR(128 ) NOT ' Stack trace at line 1 in C:\Users\reece.grover\OneDrive\XML Website\opregister.php:73: #0 C:\Users\reece.grover\OneDrive\XML Website \opregister.php(73): mysqli->query('CREATE TABLE ou...') #1 {main} in C:\Users\reece.grover\OneDrive\XML Website\opregister.php on line 73 "Thrown"

This appears to lead not only to errors, but also to SQL injection.

I learned how to bind_param, but this still prevents potential code breaking from the CREATE TABLE function.

This is my server side PHP code, the form is just a simple HTML form with Bootstrap.

//remove spaces
    $company = str_replace(' ', '', $_POST["company"]);
    //hash Password
    $password_hash = password_hash($_POST["password"], PASSWORD_DEFAULT);
    
    // Create new Operator
    $mysql = require __DIR__ . "/database.php";
    
    $sql = "INSERT INTO operators (first_name, last_name, email, password_hash, company) 
            VALUES (?, ?, ?, ?, ?)";
    
    $stmt = $mysql->stmt_init();
    
    
    if ( ! $stmt ->prepare($sql)){
        die("SQL error: " . $mysql->error);
    }
    
    $stmt->bind_param("sssss",
                        $_POST["first_name"],
                        $_POST["last_name"],
                        $_POST["email"],
                        $password_hash,
                        $company);
                         
    if ( ! $stmt->execute()) {
        die($mysqli->error . " " . $mysqli->errno);
    }
    
    
    
    //Create the new company table
    
    $sql = "CREATE TABLE $company (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(128) NOT NULL,
    last_name VARCHAR(128) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    company VARCHAR(128),
    credit VARCHAR(60),
    phone VARCHAR(60))";
    
    if ( ! $mysql->query($sql)) {
        die("Create Table Failed : " . $mysql->error);
    }
    
    $sql = "INSERT INTO $company (first_name, last_name, email, company, credit, phone) 
            VALUES (?, ?, ?, ?, ?, ?)";
    
    $stmt = $mysql->stmt_init();
    
    
    
    if ( ! $stmt ->prepare($sql)){
    die("SQL error: " . $mysql->error);
    }
    
    $stmt->bind_param("ssssss",
                        $_POST["first_name"],
                        $_POST["last_name"],
                        $_POST["email"],
                        $company,
                        $_POST["credit"],
                        $_POST["phone"]);
                         
    if ($stmt->execute()) {
    
        header("Location: signup_success.php");
        exit;

Answer 1

You should have a predefined company table instead of a separate company_name table. You also shouldn't create tables based on user input.

Create company table in advance:

CREATE TABLE companies (
  id INT PRIMARY KEY AUTO_INCREMENT,
  company_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(20)
);

Then you can INSERT the data into this table:

$stmt = $conn->prepare("INSERT INTO companies (company_name, email, phone) VALUES (?, ?, ?)");
//Example company data
$companyName = "Example Company";
$email = "info@example.com";
$phone = "123-456-7890";
$stmt->bind_param("sss", $companyName, $email, $phone);
$stmt->execute();

Each company is inserted into this table. They are distinguished by the id column.

You can also add foreign key constraints on the operators table.

ALTER TABLE operators
ADD COLUMN company_id INT,
ADD CONSTRAINT key_constraint_company_id FOREIGN KEY (company_id) REFERENCES company(id);

If you wish to delete company data when deleting an operator, you can execute DELETE CASCADE:

ALTER TABLE operators
ADD COLUMN company_id INT,
ADD CONSTRAINT key_constraint_company_id FOREIGN KEY (company_id) REFERENCES company(id) ON DELETE CASCADE;