Exact PHP Code to Insert Data into MySql Database

How to Safely and Securely Insert Data into a Database Using MySql in a Way the Eliminates MySql Inject Attacks and PHP Code that is the Shortest Most Efficient Code for Inserting Data into MySql Database

Inserting data into a database via mysql is fairly uncomplicated however the most important thing to keep in mind is security. Improperly inserting data via php mysql can result in a mysql injected attack where your database and perhaps your entire website becomes vulnerable and destroyed. With this in mind you want to regularly back up your database and store the backup offsite.

Inserting a new column into a database via php occurs with 3 steps.

1. Connect to the database and the table where you wish to insert new data.
2. Insert the new data to the database.
3. Closing the connection.

To connect to your database via php:

// connect to the database $dbconn = mysqli_connect( $host, // path to db (hostname or ip address ususally) $username, // db username $password, // db password $database // db name ); // if database connection fails if (mysqli_connect_errno()) { #insert your error handling code here# exit(); }

The second step is simplified via 2 function calls and 2 lines of code which are calls to these 2 php function.

Creating the new row for your database via php:

/* -------------------------------------------------------- Function to prepare database entries for insertion [$dbConnection]: current active database connection [$variables]: values put into columns to create new row ------------------------------------------------------- */ function sqlPrepareData($dbConnection, $variables) { foreach($variables as $key => $val) { /* $databaseEntries[$key] = the column name of the database $val = the value provided by the user */ $databaseEntries[$key] = mysqli_real_escape_string($dbConnection, $val); } return $databaseEntries; } /* ------------------------------------------------------- the function that will securely place the new data into the database table ------------------------------------------------------- */ function insertIntoDatabase($dbConn, $tablename, $userData) { /* Generate placeholders for prepared statement [$userData] is associate array ie "firstname" => "henry" with the part left of => being the exact database column name and the part right of => being value inserted in column */ $placeholders = rtrim(str_repeat('?, ', count($userData)), ', '); // Generate comma-separated list of column names $columns = implode(', ', array_keys($userData)); /* Construct the SQL query for insertion the IGNORE parameter is used to avoid duplicates without generating error IF there is a colum set as UNIQUE otherwise use $sql = "INSERT INTO $tablename ($columns) VALUES ($placeholders)"; */ $sql = "INSERT IGNORE INTO $tablename ($columns) VALUES ($placeholders)"; // Prepare the SQL statement $stmt = mysqli_prepare($dbConn, $sql); // Bind parameters to the prepared statement mysqli_stmt_bind_param($stmt, str_repeat('s', count($userData)), ...array_values($userData)); // Execute the prepared statement to insert data into the database mysqli_stmt_execute($stmt); /* feedback on success or faliure if [$affectedRows] = 0 and ][$newRowId] = 0 then we know nothing was added to database */ $affectedRows = mysqli_stmt_affected_rows($stmt); $newRowId = mysqli_insert_id($dbConn); return [$affectedRows, $newRowId]; } $vars = [ "firstname" => "steve", "lastname" => "allen", "birthdate" => "04/09/1932" ]; $userInfo = $databaseEntries = sqlPrepareData($dbconn, $vars); // function that adds new row of user profile info to the databse [$affectedRows, $newRowId] = insertIntoDatabase ( $dbconn, // active database connection "test", // name of table $userInfo // array of user provided values to be saved as new row in database ); // if [$affectedRows] == 0 or [$newRowId] == 0 then entry not added if(($affectedRows == 0 )|| ($newRowId == 0)) { // error code echo "New row not successfully added"; mysqli_close($dbconn); exit(); }

Closing the connection to your database via php:

// close the connect to database mysqli_close($conn);

Use the code struction above to put new data into a database table and inserting new rows into your database using php will be smooth, safe and efficient.