Geek Answers Handbook

I don’t quite understand SQL injection

I read a lot about SQL injection, and I understand how this can cause problems (for example: DROP TABLE __, etc.). But I'm not sure how my tutorials really prevent this. I am just learning PDO, and I think I understand.

Is this code safe from SQL injection? and why? (It takes a lot more work with these prepared statements, so I want to make sure that I’m not just wasting my time - also if the code can be improved, let me know! )

$conn = new PDO("mysql:host=$DB_HOST;dbname=$DB_DATABASE",$DB_USER,$DB_PASSWORD); // Get the data $firstname = $_POST["v_firstname"]; $lastname = $_POST["v_lastname"]; $origincountry = $_POST["v_origincountry"]; $citizenship = $_POST["v_citizenship"]; $gender = $_POST["v_gender"]; $dob = $_POST["v_dob"]; $language = $_POST["v_language"]; $landing = $_POST["v_landing"]; $email = $_POST["v_email"]; $phone = $_POST["v_phone"]; $cellphone = $_POST["v_cellphone"]; $caddress = $_POST["v_caddress"]; $paddress = $_POST["v_paddress"]; $school = $_POST["v_school"]; $grade = $_POST["v_grade"]; $smoker = $_POST["v_smoker"]; $referred = $_POST["v_referred"]; $notes = $_POST["v_notes"]; //Insert Data $sql = "INSERT INTO clients (firstname, lastname, origincountry, citizenship, gender, dob, language, landing, email, phone, cellphone, caddress, paddress, school, grade, smoker, referred, notes) VALUES (:firstname, :lastname, :origincountry, :citizenship, :gender, :dob, :language, :landing, :email, :phone, :cellphone, :caddress, :paddress, :school, :grade, :smoker, :referred, :notes)"; $q = $conn->prepare($sql); $q->execute(array(':firstname'=>$firstname, ':lastname'=>$lastname, ':origincountry'=>$origincountry, ':citizenship'=>$citizenship, ':gender'=>$gender, ':dob'=>$dob, ':language'=>$language, ':landing'=>$landing, ':email'=>$email, ':phone'=>$phone, ':cellphone'=>$cellphone, ':caddress'=>$caddress, ':paddress'=>$paddress, ':school'=>$school, ':grade'=>$grade, ':smoker'=>$smoker, ':referred'=>$referred, ':notes'=>$notes));
+4
source share
3 answers

Yes, the code is safe, since PDO will correctly screen and quote an array of parameters for you.

+4
source

Your code is safe from SQL injection because you are using a parameterized query, which basically means that after the query is created and sent to the sql server, it will be escaped, which can also be achieved using the php built-in function mysql_real_escape_string()

The following video is an excellent OWASP SQL injection info video: SQL Injection

+1
source

Rule: do not create sql manually, in which you do something like:

 sqlStatement = 'select field1, field2, field3 from mytable where index = '' + myVariable + ''

This is dangerous because if your application allows you to pass data to myVariable, it could potentially send full SQL commands to your db server.

Using parameterized queries, as you do above, is a solution.

0
source

More articles:


All Articles