php sqlite injection prevention

Question

I am putting together a fairly simple web app that uses user inputted data to act upon an sqlite database and it was brought to my attention 'sanitizing' strings was possibly not enough and could raise further problems. As I understand it I should use prepared statements. In my research I found there is PDO(php data object) that has a prepare function and also the php sqlite3 extension also offers a prepare statement. If it matters, at this point there is no login and no sensitive info in the database.

The PDO seems 'alien' to me and I do not really understand why/how I need to use it. I can copy/paste the code and get it to work, but the 'idea' of it escapes me.

So I guess the question is would the PDO OR sqlite3 prepare function be best and briefly why.

Thanks so much, Todd

deceze · Accepted Answer · 2012-06-18 13:56:53Z

The PDO extension is a wrapper that unifies access to many different databases. As long as the SQL queries you are writing in it are compatible, you can switch databases simply by connecting to a different database in new PDO(...), while not needing to touch the rest of the code.

The sqlite3 extension exclusively works with SQLite databases. If you want to connect to a different database later, you'll have to rewrite all the code that uses sqlite3 functions.

Other than that, prepared statements and how they work is basically identical between these two extensions. I'd vote for PDO, since it offers more flexibility in the long run and means you only have to learn one interface to use many different databases. Note of course that you cannot mix them, you have to use one or the other exclusively. You cannot use the prepare method of one and execute of the other.

Lawrence Cherone · Accepted Answer · 2012-06-18 14:13:13Z

Seriously if your going to start building your web apps to utilize database interaction then get some knowledge of PDO, its something worth it weight in gold and it perhaps will help you learn more about the OOP style of coding, plus as long as you use prepared statements its always safe from injection.

Without going into much detail PDO can handle many types of databases and have pretty much the same syntax, so once you build your models you can easily port to other databases without much trouble. There is a great tutorial about PDO here

<?php 
//Connect this creates a new PDO object
$db = new PDO('sqlite:/path/to/database.sdb');

//Build your sql query with parameters :field 
$sql = "SELECT * FROM sometable WHERE someCol=:field";
//Prepare the above query
$statement = $db->prepare($sql);
//Bind the value received from the form or such with the parameter place holder
$statement->bindParam(':field', $_POST['someVal']);
//Execute the prepared query
$statement->execute();
//Fetch the result
$result = $statement->fetchAll(PDO::FETCH_ASSOC);


//Treat the result as a pure array
foreach ($result as $row){
    //do something
}
?>

There are so many tutorials on the internet that explain better then i do but get to grips with PDO as you will be using it more in the future as and when the mysql_* functions disappear. Hope it helps

Collectives™ on Stack Overflow

php sqlite injection prevention

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related