sql injection

clairence

I've read a few threads on the topic and visited several other sites that discuss sql injection.

I just wondered why what seems to be a simple solution such as using ascii codes for non-alpha-numeric characters doesn't work as a preventive measure?

something like this:

$input=$_POST['input'];
$stuff=array("'","\"",">","<",".","%","&",";","=");
$codes=array("&#112","&#107","&#92","&#91","&#119","&#110","&#111","&#163","&#165");
$newvar=str_replace ($stuff,$codes,$input);

laserlight

I just wondered why what seems to be a simple solution such as using ascii codes for non-alpha-numeric characters doesn't work as a preventive measure?

It is more work than necessary and fixes the data to a particular special character encoding format. For example, what if you wanted to retrieve the data and print to a text file? Now you have to perform decoding of the data yourself.

something like this:

Your example has a bug. For example, "1.2" will be encoded to "1&#1192". Rather, it should be "1w2". Of course, you are not actually following the special character codes correctly, which means that you have invented a proprietary format.

Basically, use the appropriate escaping mechanism for your database API, e.g., prepared statements, mysql_real_escape_string(), etc.

MarkR

This is a daft way of doing it, you're storing junk in your database for those characters.

You should always store data in the database in its original, unprocessed format. Then if you need to modify it for display, do so at display time.

The best way of preventing SQL injection is to use APIs which are not vulnerable to it (i.e. prepared queries).

The second best way is to escape every string in your SQL with the appropriate escaping function.

The worst way is to mess the data around in some way so that they can't be used to do SQL injection - because it has the side effect of breaking your data.

Mark

igorlopez

Mark,
I have just started learning php and am also concerned about SQL injections.
Can you elaborate a little on what you mean by

use APIs which are not vulnerable to it (i.e. prepared queries)

laserlight

Take a look at what the PHP manual has to say about the PDO extension. This extension includes support for prepared statements (prepared queries).

igorlopez

Tanks a lot 😃 , exactly what I was looking for.