Avoiding SQL Injection in SQLite3

Question 1

I'm trying to figure out a good easy way to avoid SQL Injection and so far I've only been able to come up with two ideas:

Base64 encode the user input (Don't really want to do this)
Use regex to remove unwanted characters. (Currently using this, not sure if it's 100% safe)

Here is my current code:

 <?php
 $hash = $_GET['file'];
 if (isset($hash))
 {
 $db = new SQLite3("Files.db");
 if ($db != null)
 {
 $hash = preg_replace('/[^A-Za-z0-9 _.\-+=]/', '_', $hash);
 if ($response = $db->query("SELECT [FILE] FROM '$hash'"))
 {
 echo $response->fetchArray()[0]; // File name is returned if successful.
 }
 }
 }
 ?>

My question is am I going about this the right way or are there any better ways to do this?

Question 2

Use PDO with parameterized queries.

Question 3

possible duplicate of How can I prevent SQL injection in PHP?

Question 4

Get your database library to do it for you. The PHP Sqlite3 library supports prepared statements:

$stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);

Question 5

I voted as best answer because i didn't know about this. Even though it won't work for the code I provided I will be changing my code to implement this. (Just started learning about SQLite yesterday, still lots to learn)

Question 6

+1 it's a good idea to learn to use parameters. It's safer and easier than trying to copy PHP variables into SQL strings.

Question 7

The common advice to protect against SQL injection is to use prepared queries and use parameters to combine dynamic content into your SQL expressions.

But this only works if you use the dynamic content in place of a single scalar value, like a number or a quoted string or date literal.

In your case, you show a PHP variable replacing a table name in the FROM clause. You can't use a parameter for this usage. In fact, you show a usage that won't work in any case, because you're quoting the table name with single-quotes, as if it's a string literal. I don't think SQLite supports this.

To help protect against SQL injection for a dynamic table name, the best practice is to use whitelisting. That is, compare the input against a list of known valid table names, and if the user tries to enter anything that's not in that list, reject it.

<?php
$valid_tables = array('table1', 'table2', 'table3');
$hash = $_GET['file'];
if (array_search($hash, $valid_tables)) {
 ...do your query...
} else {
 ...issue an error or else some default behavior...
}

You may create the $valid_tables array by hard-coding it, reading a config file, or you may query your SQLite database for all the currently existing tables.

joews 30.4k11 gold badges82 silver badges91 bronze badges · Accepted Answer · 2014-03-20 20:32:39Z

7

Get your database library to do it for you. The PHP Sqlite3 library supports prepared statements:

$stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);

Share

Improve this answer

answered Mar 20, 2014 at 20:32

joews's user avatar

joews

30.4k11 gold badges82 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Smith

John Smith Over a year ago

I voted as best answer because i didn't know about this. Even though it won't work for the code I provided I will be changing my code to implement this. (Just started learning about SQLite yesterday, still lots to learn)

2014年03月20日T21:10:44.717Z+00:00

Bill Karwin

Bill Karwin Over a year ago

+1 it's a good idea to learn to use parameters. It's safer and easier than trying to copy PHP variables into SQL strings.

2014年03月20日T21:47:58.697Z+00:00

CollectivesTM on Stack Overflow

Avoiding SQL Injection in SQLite3

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Hot Network Questions

CollectivesTM on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Related