search

Home  >  Q&A  >  body text

Company name registration form

<p>For reference only, new to coding, self-study, relax...</p> <p>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. </p> <p>Everything is working fine except for the vulnerability in the table name variable. </p> <p>I'm using PHP and MySQL. </p> <p>I initially had issues if the company name had a space between the 2 words, but I fixed that using str_replace</p> <p>I did a lot of testing and found that if I put the company name in "out" it breaks the code. </p> <p>I received this error message: </p> <p>"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"</p> <p>This appears to lead not only to errors, but also to SQL injection. </p> <p>I learned how to bind_param, but this still prevents potential code breaking from the CREATE TABLE function. </p> <p>This is my server side PHP code, the form is just a simple HTML form with Bootstrap.</p> <pre class="brush:php;toolbar:false;">//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;</pre></p>
P粉904191507P粉904191507485 days ago616

reply all(1)I'll reply

  • P粉186897465

    P粉1868974652023-09-06 15:21:05

    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:

    1

    2

    3

    4

    5

    6

    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:

    1

    2

    3

    4

    5

    6

    7

    $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.

    1

    2

    3

    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:

    1

    2

    3

    ALTER TABLE operators

    ADD COLUMN company_id INT,

    ADD CONSTRAINT key_constraint_company_id FOREIGN KEY (company_id) REFERENCES company(id) ON DELETE CASCADE;

    reply
    0
  • Cancelreply