SQL Injection

Protecting against

[henry0]

As I was looking for info about protection against SQL injection
I found a few items
What do you think?
The first two are quite obvious, but how good of a doable effect will have or could have the third script?

Not to mention good link rewriting.

#########
A more secure way to compose a query for paging
<?php

settype($offset, 'integer');
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";

// please note %d in the format string, using %s would be meaningless
$query = sprintf("SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET %d;",
$offset);

?>
############

############

if(isset($_GET['page'])) {
$page = $_GET['page'];
} else {
$page = "main";
}
and change it to

if(isset($_GET['page'])) {
$page = addslashes($_GET['page']);
} else {
$page = "main";
}
Another possibility is to use str_replace() or regex, but addslashes() does the trick just fine. You might also want to look into escaping periods as well, to prevent against the "../" parent directory bull#*$!. Basically, escape your GPC (GET/POST/COOKIE) input.
##############

##############

<EDIT> FYI I am not the script generator and did not test it!</EDIT>
//START OF FILTER - SWAP WORDS
$hack = $wordthatneedsfiltering;

//Begin filtering $hack of html tags and dangerous funcs
$hack = htmlspecialchars($hack);
$hack = strip_tags($hack);
$hack = str_replace("select", "", $hack);
$hack = str_replace("SELECT", "", $hack);
$hack = str_replace("update", "", $hack);
$hack = str_replace("UPDATE", "", $hack);
$hack = str_replace("delete", "", $hack);
$hack = str_replace("DELETE", "", $hack);
$hack = str_replace("drop", "", $hack);
$hack = str_replace("DROP", "", $hack);
$hack = str_replace(";", "", $hack);
$hack = str_replace("--", "", $hack);
$hack = str_replace("\0", "", $hack);
//Finish filtering $hack of html tags and dangerous funcs

//Begin filtering $hack of non alphanumeric characters
$hack = preg_replace("/[^0-9a-z -#]/i",'', $hack);
//Finish filtering of non alphanumeric characters

//Begin filtering $hack of non numeric characters
//Use this when only wanting the numbers in the variable
$hack = preg_replace("/[^0-9 -#]/i",'', $hack);
//Finish filtering of non numeric characters

$wordthatneedsfiltering = $hack;
//END OF FILTER - SWAP BACK

[hakre]

there are different approches (as your wrote and quoted) to the problem. i think first of all, the query has to be secured, meaning any input from the user side has to be checked, secured and ensured.

i would not rely on "general purpose" scripts for that problem, because they try to shield your scripts but i don't think they will work 100%. as mentioned, the coder her/himself has to ensure input is correct (this is not only a sql issue i think).

your first example is in the right direction. the principle is to ensure that

$offset

is an integer. this can ensured with intval() [php.net], too:

$offset = intval($offset)

this is the one case for a query. the second are string values. they need to be escaped if you would like to use them securely. as mentioned in your second example, addslashes() [php.net] will do the trick. by reading some other docs about sql injection, it was mentioned, that if you want to be 100% secure, the build in funtion of the database itself should be used. for mysql and php this is mysql_real_escape_string [php.net], use it instead. it's even characterset safe and needs a connection to your database.

so this is about creating values you can savely insert into your sql queries while preventing injections. additionally you can perform other checks based on syntax (is it a true email adress?; is the username or password to short / long?; is this really a date?; etc.).

in my opinion these rules are strict and much better then a general "hey there might be something like sql in the vars". the third example is something like that. even the intention might be right, but bad coding is bad coding so the person in charge (programmer) need to take care first, not a general script.

for perl there are some more approches. there is a routing which can pre-check the syntax of a query first. this is another way to ensure, that a query is working and correct. i don't know such a function for php.

my 2 cents,
hakre

[henry0]

Thank you hakre
Very interesting conversation

While researching I found tons of links
But what really should scare (I was) most readers is that the protections scenarios are demonstrated by using attack examples.

Therefore
1)Giving the reader goose bumps
2)Offering a “how to inject tutorial” if you read the papers with wrong intentions in mind

I think most of us should do a search for SQL injection protection or similar terms and get a good serving of food for thoughts
Henry

[hakre]

okay, most of the docs approach by showing sql injections, mostly for specific software.

[unixwiz.net...]

this document explains in a more widely an general way how sql injection works (you need to know that to secure your scripts) and offers ways how to prevent sql injections. so this was a must read for me, because you never know everything.

it seems to me that sql injection is becomming a smash-word right now, but it was everytime the prior aim for all web applications that they should ensure 100% each input. thats something many people did not do, so their scripts have a lot of "features" implemented they don't know of. for most of them, they need to re-code their whole apps right now.

this reminds me a bit of the discussion about php and register globals. some people talked so much about that it is insecure to have that feature enabled, they really missed security completely, because they did not know why it could be insecure. and switching register globals off does not mean to make anything more secure. that was a problem and still is and i hope that with the topic sql injection php coders will approach a bit more wisely.

[redzone]

Hakre,

Interesting read, it opened my eyes a bit.
I tried to break into my login area using their attack methodologies, and was only able to generate CGI 500 errors, but that doesn't mean I'm fully protected, and I'm going to play some more..

I think one of the most obvious implementations is not to use obvious table and column names in your DB architecture. (Also, you wouldn't believe how many CGI programs I take over maintenance on, that have SQL calls that are not "read only" for Login forms, etc...)

[redzone]

hakre,

On a further note, when inserting values from form fields into SQL Select statements, wouldn't executing a String Replace looking for spaces, and replacing with "" take care of most of those injection techniques discussed in the article you linked to?

Replacing spaces in the form field value would run everything together and invalidate any add on SQL commands?

[hakre]

@redzone: yes, shure. you know what's even more secure? take any input from the user and make an empty string out of it. ups ;). it can be the solution but must not. and you yourself has to decide. if you're choosing a general solution, you don't decide in each case. this is making your code unsecure. \x20 or similar can be something like space for databases, too. taking the escape routine of the database itself will help you a lot more then trying to make the same thing better. and an update on the db will still do the job in the future, your code maybe not.

[jamie]

hi redzone:

>> that have SQL calls that are not "read only" for Login forms, etc...)

sometimes that is not practical. what happens if a user submits a review - you need to have insert permissions on the db? or have i misunderstood?

cheers

[redzone]

Jamie,

I was referring to CGI forms where there is no "Add/Update" in the process.

[hakre]

redzone is right. this is another important thing to reduce the possibilities of sql injections: logon to database as a user with the minumum of required rights. for example for querieing an article, take a user who can only select from the one table/db, you need and not one with full persmissions on all databases.