June 17, 2013

Insert value to MySQL table safely using PDO

FriedBitz’s Question:

Would this protect my INSERT from SQL Injections? and can I somehow shorten this code to make it look neater?

$db = new PDO('mysql:host=XXXXXXXXXX;dbname=XXXXXXXXX', 'XXXXXXXXX', 'XXXXXXXXXX');

// query MySQL to verify login
$query = $db->prepare("INSERT INTO login (username,password,name,email_add,age,country) VALUES (:username,:password,:name,:email_add,:age,:country)");
$query->execute(array(
':username' => $username, 
':password' => $password,
':name' => $name,
':email_add' => $email,
':age' => $age,
':country' => $country));

YES, PDO Extension already removes all the injection vulnerabilities like that. Since you are binding params afters ward, you are doing it correctly too.

However, just to make the query at it safest state, specify the data type of the variable while binding them.

$query = $db->prepare('INSERT INTO login (username,password,name,email_add,age,country) VALUES (:username,:password,:name,:email_add,:age,:country)');

$query->bindParam(':username', $username, PDO::PARAM_STR, 20);
// and so on
$query -> execute();

More Details

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!