Does this simple function add something to prepared statements?

Question

I've been researching how to completely prevent against SQL injection, but there are so many different forms of injecting that completely preventing it looks impossible to me. I am using prepared statements and put up the following simple function to prevent against SQL injections. Does this secure against (the most) SQL injections, if I run this function on every posted variable? I have also read about multi-byte characters, how can I secure my queries against that?

function securevar($var)
{
    $var = str_replace("'", "'", $var); //secure '
    $var = str_replace('"', """, $var); //secure "
    return $var;
}

Answer 1

No, it doesn't, use prepared statements as is plus single query functions and proper input validation. The latter one is the default for most functions in PHP (e.g. mysqli::query Vs. mysqli::multi_query).

Prepared Statements

Prepared statements will forward the data of a variable and its type separately to your DBMS which in turn will either escape all offending characters on your behalf or parse the data independent of the actual query (depending on the DBMS). There's nothing you have to know about which characters need to be escaped and which don't. This is the most secure way to achieve this.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli(null, null, null, "database");
$mysqli->prepare("SELECT * FROM `users` WHERE `id` = ?");
$mysqli->bind_param("d", $_GET["id"]);
$mysqli->execute();
// ...

All of this complexity can be abstracted, have a look at e.g. Drupal source code.

Single Query Functions

Single query functions ensure that a query of yours will always only yield a single query that will be passed to the DBMS. Effectively killing any possibility that somebody ends your query and executes another; a typical scenario with SQL injection, example:

<?php

$_GET["id"] = "1;SET foreign_key_checks=0;DROP TABLE users";

$mysqli->multi_query("SELECT * FROM `users` WHERE `id` = {$_GET["id"]}");

Validation

This is perhaps the most important advise and is always important, no matter what you try to achieve. Use PHP's built-in filter_* functions and check other big project's for validating stuff that isn't covered by these functions.

Combined Example

Let's put it all together.

<?php

// max_range default is PHP_INT_MAX.
$userId = filter_input(INPUT_GET, "id", FILTER_VALIDATE_INT, array(
  "options" => array("min_range" => 1),
));

// Contains either a valid integer now or either FALSE or NULL.
if ($userId === null || $userId === false) {
//if (!$userId) {
  throw new \InvalidArgumentException();
}

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli(null, null, null, "database");
$stmt = $mysqli->prepare("SELECT `name` FROM `users` WHERE `id` = ?");
%stmt->bind_param("d", $userId);
$stmt->execute();
$stmt->fetch($userName);
$stmt->close();
$mysqli->close();

exit("Hello {$userName} with {$userId}.");

Answer 2

No. SQL Injection should be prevented by parameterizing your queries.

OWASP has some great resources to help you with this.

Here is one such sheet to show this in several languages. The PHP cheat sheet also has some more information regarding sql injection.

To directly address your hard coded filter function check out this source demonstrating the exploitation of hard coded filters, including quotes and single quotes.

Answer 3

As @Douglas and @fleshgrinder said before, no.

Consider the following request:

SELECT title, news FROM news_table WHERE id = $id

Applied for $id = 5 is fine, now if $id = 5 OR 1=1 will display all the news... Not a big deal?

Now if $id = 5 UNION SELECT login as title, password as news from secret_credentials_table where is_admin = 1 should also be a valid SQL request, with no quotes ...

• Use prepared Requests
• Typed arguments also helps (but is not the silver bullet either!)

For the typed argument, if you ensure that your $id is an integer, then such an injection is impossible. But of course, some request expect string as input, so you lose all the benefits of this.