Avoid SQL Injection in SQLite3
I'm trying to find a nice easy way to avoid SQL Injection and so far I could only come up with two ideas:
- Base64 encodes user input (don't want to do that)
- Use a regular expression to remove unwanted characters. (Currently in use, 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 doing this the right way, or are there any more efficient ways to do this?
source to share
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);
source to share
General guidelines for protecting against SQL injection is to use prepared statements and use parameters to combine dynamic content into your SQL statements.
But this only works if you are using dynamic content instead of a single scalar value like a number or quoted string or date literal.
In your case, you are showing a PHP variable replacing the table name in the FROM clause. You cannot use a parameter for this use. In fact, you are showing a usage that will not work in any way, because you are quoting the table name with single quotes as if it were a string literal. I don't think SQLite supports this.
To provide protection against SQL injection for a dynamic table name, the best practice is to use a whitelist. That is, compare the input with a list of known valid table names, and if the user tries to enter something that is not on 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 can create an array $valid_tables
by copying it by reading the config file, or you can query the SQLite database for all existing tables .
source to share