MySQL INJECTION Solution

Question

I have been bothered for so long by the MySQL injections and was thinking of a way to eliminate this problem all together. I have came up with something below hope that many people will find this useful.

The only Draw back I can think of this is the partial search: Jo =>returns "John" by using the like %% statement.

Here is a php solution:

<?php
function safeQ(){
   $search= array('delete','select');//and every keyword...
   $replace= array(base64_encode('delete'),base64_encode('select'));
   foreach($_REQUEST as $k=>$v){
      str_replace($search, $replace, $v);
   }
}
safeQ();

function html($str){
   $search= array(base64_encode('delete'),base64_encode('select'));
   $replace= array('delete','select');//and every keyword...
   str_replace($search, $replace, $str);
}

//example 1
...
...
$result = mysql_fetch_array($query);
echo html($result[0]['field_name']);

//example 2
$select = 'SELECT * FROM $_GET['query'] '; 

//example 3
$insert = 'INSERT INTO .... value( $_GET['query'] )'; 


?>

I know, I know that you still could inject using 1=1 or any other type of injections... but this I think could solve half of your problem so the right mysql query is executed.

So my question is if anyone can find any draw backs on this then please feel free to comment here.

PLEASE GIVE AN ANSWER only if you think that this is a very useful solution and no major drawbacks are found OR you think is a bad idea all together...

Answer 1

This has already been implemented correctly in PHP:

• mysql-real-escape-string

However, you should start by reading the PHP manual entry on SQL injection.

---

To answer your actual question, the primary drawbacks of your approach are:

1. It doesn't work
2. Just to be clear, it doesn't work, and will leave you wide open to SQL injection.
3. It also prevents you entering any data which clashes with a MySQL keyword.

If you're going to reinvent the wheel, I would recommend looking at pre-existing wheels.

Answer 2

Reinventing the wheel and reinventing it the Wrong Way (TM).

• First of all, there are parametrized queries (available for PHP in MySQLi extension); if that's not an option, there's mysql_real_escape_string. This is the main issue - check for already available options before deciding to implement them on your own.
• Second, you are trying to call PHP functions in SQL, what you wanted was probably something like 'SELECT * FROM ' . safeQ($_GET['query'])
• Third, you've broken all indexing and search on data containing your "evil words", say hello to performance problems and crazy workarounds.

Edit: To address the example you're giving in comments:

$v="1; DROP tbl;\";DROP tbl" // oh look, an SQL injection attempt!
$s = 'SELECT * FROM tbl WHERE ID='.$v; // SQL injection, no doubt

// if ID is an integer field, make it an integer. Simple, secure, and fast.
$s = 'SELECT * FROM tbl WHERE ID='.(int)$v; 
// $s == 'SELECT * FROM tbl WHERE ID=1' // see PHP manual for explanation of type casting

// if ID is a string field, escape it. Simple, secure, and still plenty fast.
$s = 'SELECT * FROM tbl WHERE ID="'.mysql_real_escape_string($v) . '"';
// $s == 'SELECT * FROM tbl WHERE ID="1; DROP tbl;\";DROP tbl"'; 
// See? No injection, as the quote is *escaped*

Answer 3

It is a bad idea.

There is already a proper, correct, and working, solution to SQL injection in every language, platform, runtime, engine, library, whateveryoumighthave.

It's called parameters.

Instead of placing the value into the SQL as a literal constant, you add a parameter placeholder, and then provide the paramter value alongside the query, and not using any formatting tricks to get it back into the SQL either.

Every other solution which tries to "fix" the problem by looking at the SQL or pieces of it and attempting to determine if it is correct is sub-par and most of them has other bugs and problems that you can't see, don't know about, and don't really want to have.

So stop beating around the bush, and do it the right way to begin with. Switch to using parameters.

In PHP, talking to MySQL, there are multiple ways, but using the mysqli library:

$stmt = $db->prepare('SELECT * FROM products WHERE id = ?');
$stmt->bind_param('i', $id);
$stmt->execute();
.. bind for results
$stmt->fetch();

If the $id variable here contains unsafe code, it will make the SQL Execution crash, because if id is an integer, it cannot be compared to anything but another integer, and if it is a string, it will be compared against the string you provide, not trying to execute the string as part of the SQL.

Now, the comment brings out a point, sometimes you have to change the SQL anyway, because you will provide dynamic ordering (ie. ordering picked by the user), or dynamic filtering (ie. which filters to apply are picked by the user).

In this case, the solution is simple: Do not take any user-provided text and place it into the SQL. Instead, you hard-code the constants to add to your SQL into your code, and let the users choices guide the code in which of those constants to add, in which order, and where.

Answer 4

Look, you're thinking of it from the wrong point of view.
Though many people do. There are 2 strong misbeliefs according to SQL injections:

1. There is some special attack, called "SQL injection".
2. To prevent it, input data must be threaten some special way.

Both of them wrong.

There is not such thing as "SQL injection" when you follow SQL syntax.

Go figure.

If you follow SQL syntax rules (and you must follow it anyway, despite of injections!) you are safe, just as a side effect.

Any string data put into SQL query must be treated with 2 rules:
1. all special characters in the string must be escaped.
2. the string must be enclosed in quotes.

There are also issues with non-textual data. Not a big deal too.
But if you feel it too complicated, you can always go for the prepared statements - it is always safe and allow no thinking.

Though there are still issues with non-data parts of the query. That's most danger part indeed. And the only issue have the right to be called "SQL injection".
To avoid it, all dynamic non-data parts of the query must be hardcoded in your script. That's the only possible way.