Menu
Possible Duplicate:
How can I prevent SQL injection in PHP?
This is the example on w3schools.org:
<html> <body> <form action="insert.php" method="post"> Firstname: <input type="text" name="firstname" /> Lastname: <input type="text" name="lastname" /> Age: <input type="text" name="age" /> <input type="submit" /> </form> </body> </html> <?php $con = mysql_connect("localhost","peter","abc123"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("my_db", $con); $sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "1 record added"; mysql_close($con) ?> I've read through other questions on here, but I couldn't find a direct answer, as most were much more complicated.
I looked at How can I prevent SQL injection in PHP?, but I'm a bit confused on how to modify this:
$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)'); $preparedStatement->execute(array(':column' => $unsafeValue)); Assuming I used the HTML form above and wanted to insert the data from field 'firstname' into the database, should it look like this? Or am I supposed to modify column?:
$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)'); $preparedStatement->execute(array(':column' => $firstname)); 
234
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
You can also use INSERT ... TABLE in MySQL 8.0. 19 and later to insert rows from a single table. INSERT with an ON DUPLICATE KEY UPDATE clause enables existing rows to be updated if a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY .
The example you provided inserts the post vars into the database without first analyzing them for evil user input. Use type casting, escaping/filter functions, prepared statements etc. before using them to interact with your DB.
A general rule to go by is to never trust user input. EVER!
Check out: Best way to stop SQL Injection in PHP
In response to your question, here is how you'd handle the entire form using PDO prepared statements.
$stmt = $db->prepare('INSERT INTO Persons (FirstName, LastName, Age) VALUES (:first_name, :last_name, :age)'); $stmt->execute(array(':first_name' => $first_name,':last_name' => $last_name, ':age' => $age)); If you just want to insert one column in the record like you asked, the syntax would be:
$stmt = $db->prepare('INSERT INTO Persons (FirstName) VALUES (:first_name)'); $stmt->execute(':first_name', $first_name); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
