What are your methods for preventing sql injection? For example, say I am adding $value into my database. What is the best way to make sure that if $value contains " ’ " it is inserted itno the database, but does not break the query?
by checking get_magic_quotes_gpc() you can find out whether the incoming variable was escaped (some characters got a \ in front of them) or not.
Now that you know that, you can clean up the variable (if it was escaped) by calling stripslashes(), so that you definetly have an unescaped string.
You must not insert raw (unescaped) variables in the database, so you have to escape it again, this time using the proper function for the escape to be valid (different databases use different escaping), for mysql that would be mysql_real_escape_string().
I am using ADODB library for DB access and use it’s methods, which internally call a proper escaping function, so that my code is as database-independant as possible.
Regards
addslashes http://us2.php.net/manual/en/function.addslashes.php
I think some say this one is better:
mysql_escape_string http://us2.php.net/manual/en/function.mysql-escape-string.php
be aware if magic quotes is turned on
http://us2.php.net/manual/en/function.get-magic-quotes-gpc.php
Addslashes is vital, but still isn’t a brick wall against injection attacks.
For example:
SELECT * FROM TABLE WHERE privilages=1 AND article=[user-entered]
Could be altered to:
SELECT * FROM TABLE WHERE privilages=1 AND article=22 OR privilages=123
This version doesn’t suffer from that exact problem:
SELECT * FROM TABLE WHERE (article=[user-entered]) AND privilages=1
Basically, it pays to think about how the user-entered portion of a query might be manipulated. A more simple solution than the above would be to check to make sure the user enterd value was numeric, or use some non-standard SQL (that happens to work with MySQL) and surround the number with quotes.
:tup:
If you know in advance what the value is supposed to contain or not contain, then by all means make sure it is valid before proceeding to involve it with your DB. Always.
It really depends on the querys. There isn’t a prevent_sql_injection function (although I really wish there was!)
Very true.
It is probably possible in some database setups to make sql injection attacks impossible, even without using addslashes or any sort of validation. For example, if you put limitting conditions in a join rather than in a where clause. I’m not saying that you should rewrite your queries just so that you don’t have to check input, but I think it is interesting to muse about.
If you read the manual on sql injection you will see just how firghteningly easy it is to do, and just how extensive the damage can be.
The first solution is to create a mysql user with read-only access to only limited columns and tables and to use this user account to connect for the queries for your dynamic page content and user search functions. This will hide much of your schema from the public as well as preventing many of the common attacks like add user or extend privileges.
Then create other specific user accounts with write access to only those table columns that absolutly demand it. Where possible, place all pages that save user input into protected folders that require authentication to access.
Wherever you have to accept user input data for addition to the database, do all the things like mysql_real_escape_string, but also test the input with regular expressions etc that look for SQL operators and clause constructions. Then save the input into work tables in a seperate database so that your main database is not subjected to the attack directly. Finally use private scripts that transfer the input from the intermediate database to your main database. If the work database has been successfully attacked these scripts should fail and then alert you.
Since i don’t need to sort anything or do any searches, i convert everything to hex using bin2hex(), then insert it into the db.
I’m sure this isn’t the correct way to do it…but if it works, why not.
What are the benefits of doing that?
I was wondering the same thing… although he is not the first person I have heard that does this.
I believe that as long as you verify the incoming data, the first query is also injection-proof. If the parameter is supposed to be an integer (as it is in this case), a simple is_int() or a cast would prevent the query to be executed. As for the string values, they are already escaped when they enter the query (assuming some safe sort of escaping is used), so it’s safe again.
And also, if your database supports quoted integer parameters in query, it’s probably a good practice to use this method. In that case, the query
SELECT * FROM TABLE WHERE privilages=1 AND article=‘[user-entered]’
Could only be altered to:
SELECT * FROM TABLE WHERE privilages=1 AND article=‘22 OR privilages=123’
which would fail again. Dunno about other databases, but Mysql and Postgre both support such syntax.
What are the benefits of doing that?
well, hex is all numbers no matter what, so they can input any character they want, and it wont break a URL, mysql query, or anything. You dont have to worry about escaping characters, tags, etc.
Works really well for me.
All you really need to do is watch for things like overflows (just substr everything, just to be sure… never rely on anything except your own code) and addslashes EVERYTHING that touches a database.
i’m not sure how much we can go on about a topic like this.
it’s more like, you learn how to do it right…then you do that for all your websites.
mysql_real_escape_string has always done the trick for me.
it’s generally good to turn off magic quotes. this way you have a little more power over what happens by default.
just remember the rule.
addslashes() on input.
stripslashes() on output.
Here are the functions I use all the time. You will notice that I don’t check for datatype in these functions for various reasons (due to conditionnals that are specific to some portions of the code –> checks made in the relevant scope).
//check if incomingData is not empty and of the expected length
function checkIncomingData($idata, $minsize, $maxsize)
{
if (
strlen($idata)<$minsize
or
strlen($idata)>$maxsize
)
{
return false;
}
else
{
return true;
}
}
//make sure that nothing bad can be entered by the user (-->sql injection attack)
function cleanIncomingData($idata)
{
$cleaned = trim($idata);
$cleaned = mysql_real_escape_string($cleaned);
return $cleaned;
}
Just a little note: mysql_real_escape_string() is a little more thorough than addslashes().
RE: Postgre, from
the manual
Example 27-2. Splitting the result set into pages … and making superusers (PostgreSQL)
<?php
$offset = $argv[0]; // beware, no input validation!
$query = “SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;”;
$result = pg_query($conn, $query);
?>
Normal users click on the ‘next’, ‘prev’ links where the $offset is encoded into the URL. The script expects that the incoming $offset is a decimal number. However, what if someone tries to break in by appending a urlencode()'d form of the following to the URL
0;
insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)
select ‘crack’, usesysid, ‘t’,‘t’,‘crack’
from pg_shadow where usename=‘postgres’;
If it happened, then the script would present a superuser access to him. Note that 0; is to supply a valid offset to the original query and to terminate it.
This works because Postgre supports multiple queries.
Escaping quotes is so that the user cannot include one in the input to terminate the input string so that any additional SQL clauses or statements will be processed. But that is only one way to mess with your DB as the above example shows.
geniusgoalie It really depends on the querys. There isn’t a prevent_sql_injection function (although I really wish there was!)
Have a look at this SafeSQL class
I think you misunderstood me. My point in the previous post was to enclose variables in quotes even if they are of type int, and escape the contents.
Your code would become
<?php
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET '$offset';";
?>
Note using the quotes around the $offset in the query. Now whatever $offset might user set, its either a valid offset (thus a valid query) or and invalid query, but you cannot inject any sql code in here. Provided the $offset is escaped, as I stated in my previous post.
Regards
But that all depends on whether your db engine will accept quoted integers, some will some wont.
PS not ‘my’ example, from the manual. :good: