How can I prevent SQL injection in PHP?

Google+ Pinterest LinkedIn Tumblr +
Want create site? Find Free WordPress Themes and plugins.

With the current rapid evolving technology and websites popping up left and right security is always a big concern.
Lately there is a lot of talking about securing websites with regards to SQL injections and such, i’ll present you some of the better solutions.

Prepared statements with PDO – Setting up

If you are using any supported PDO connection you should start by setting up the connection correctly:

$db = new PDO(‘mysql:dbname=yourdb;host=127.0.0.1;charset=utf8’, ‘username’, ‘password’);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The first line creates your PDO connection as usual. The second will disable PDO’s emulation of statements and instead will use real prepared statements. Finally the third line will disable you script failing with “Fatal Error” and throws “PDOException” instead (you may or may not use this line depending on your needs).

Prepared statements with PDO – Execute statement

Once you have your PDO connection see the following examples:

$stmt = $db->prepare(“INSERT INTO REGISTRY (name, value) VALUES (:name, :value)”);
$stmt->bindParam(‘:name’, $name);
$stmt->bindParam(‘:value’, $value);

// add row
$name = ‘one’;
$value = 1;
$stmt->execute();

// add another row
$name = ‘two’;
$value = 2;
$stmt->execute();

It’s as simply as that.

Prepared statements with MySQLi

If you are using MySQLi as you DB driver this example shows you how to bind parameters safly to your querys:

// Prepared statement, stage 1: prepare
if (!($stmt = $mysqli->prepare(“INSERT INTO test(id) VALUES (?)”))) {
echo “Prepare failed: (” . $mysqli->errno . “) ” . $mysqli->error;
}

// Prepared statement, stage 2: bind and execute
$id = 1;
if (!$stmt->bind_param(“id”, $id)) {
echo “Binding parameters failed: (” . $stmt->errno . “) ” . $stmt->error;
}

if (!$stmt->execute()) {
echo “Execute failed: (” . $stmt->errno . “) ” . $stmt->error;
}

Conclusion

Preparing safe SQL statements isn’t hard to do but requires you to have some base knowlage about this topic and be very exact on building your database adapter.

Here are some useful links you may want to explore with further reading and more examples:

Facebook Comments
Did you find apk for android? You can find new Free Android Games and apps.
Share.

About Author

I'm a passionate IT specialist focussed on project management and application development. Used to work in one of the largest IT infrastructes inside a single complany within europe. Since 2010 I switched to leading the development of the software Contilas. With a broad set of skills and accumulated knowlage I want to share some of it!

Leave A Reply

SHARE
OR
Subscribe
Before you leave - make sure to subscribe to my youtube channel or you will miss awesome videos! To stay in toch like on Facebook or follow on Twitter! You can also leave your eMail to get the newsletter!