SitePoint Sponsor

User Tag List

Page 2 of 4 FirstFirst 1234 LastLast
Results 26 to 50 of 88
  1. #26
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    naperville
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First, if you're executing one query, make sure that the user isnt executing multiple - search the string for a ; on RDMS that support multiple queries.

    Quote Originally Posted by duuudie
    why would anyone use quote around integers?

    Suppose you must move a db that doesn't let you go away with it (not like mySql does), what will you do? If you're using a class to deal with your db, it's a matter of seconds to switch to a new db. With quoted integers, you might end up re-writing all your queries.
    Database portability by switching classes is a myth. First, it would only work if you used very basic queries. Complex joins, table creations, anything remotely complicated will need to be tweaked for optimization at the least, others will just not work without a rewrite. When you switch DB's, you'll at the very least need to tweak all your queries to get the performance you had in the first, although you'll find that some will need to be rewrote. If stored procedures and views are available, use them! If you dont, you're hindering yourself.

    With this fact in mind (that you'll need to edit your queries at the least), use the available features of the DB to improve your application.

    If you intend on switching to oracle, everything will need a massive rewrite.

    I use DAOs and keep all my queries in one place - then, you load the correct set of DAOs depending on the database. This way, your business logic is independant of where the data came from.

  2. #27
    SitePoint Zealot ceefour's Avatar
    Join Date
    Feb 2005
    Location
    Bandung, Indonesia
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    God. I really can't believe something can go on for ages just because of a question like this. Not to mention: "... that quoting is the absolute most simple way of making your queries bulletproof against any sql injection attack." Quoting = bulletproof? Far from it. Putting quotes around a value won't stop that value to have quotes inside.

    I think the only thing that makes SQL injection so popular is because people don't know it exists, even though they know, they don't know how to properly handle it (oh yeah... by putting quotes around?)

    On MySQL, use mysql_escape_string() on *ANY* data you want to input to the database. Even the ones not from the user. There are times you want to input exotic (i.e. containing quotes, binary data, etc.) so you should always quote things anyway. If you have magic quotes turned on, stripslash it first then mysql_escape_string it. magic_quotes_gpc DOES NOT ESCAPE YOUR DATA. Really. addslashes and mysql_escape_string works differently, they have different rules for different types of characters. And different databases work differently.

    And yeah, you don't stripslash on output. Why should you? The only reason why you should stripslash something is because you made it addslashes in the first place (because of magic quotes). If you retrieve the data from the database you don't need stripslash, but in most cases you need htmlspecialchars (assuming you output as HTML).

    If you use another database other than MySQL, please note that not all database drivers support an escaping function, at least oci8 doesn't have one AFAIK. If you look at oci8's manual or even some other database driver's manual they blatantly use variables directly inside queries. Not sure if they intentionally teach you how to allow SQL injection in your scripts but I guess it's their problem and your misfortune for following their paths.

    In any case the better way is to use a good abstraction layer like MDB ( http://pear.php.net/package/MDB2 ). It has escaping function for all drivers even oci8, and you don't even need to know "do I have to escape an int or not" because all you do is call $mdb->quote($variable, 'integer') and the driver will do it for you, quoting if needed or just plain casting to int if the database driver doesn't like quoted ints. As simple as that.

    After throwing the bulk of response above, now I'm questioning myself the whole meaning of life...

  3. #28
    SitePoint Zealot ceefour's Avatar
    Join Date
    Feb 2005
    Location
    Bandung, Indonesia
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As a proof that addslashes() and mysql_escape_string() works differently, try inputing a binary data on your database. Yeah, binary, like an image or something (preferably not so small), use a blob field. If you only use addslashes() or rely on magic_quotes_gpc to escape your binary data most likely your query will choke. mysql_escape_string will work fine no matter what kind of data you gave to it.

    The ultimate and only solution to SQL injection is [properly] escape your data using the proper function. The end. There's nothing to it. You don't have to check for ANY character whatsoever. Just pass it to the escaping function and you're done. There's no SQL injection will get away with it..... not unless the escaping function is buggy.

    The same thing goes for XSS (cross-side scripting), if you always use htmlspecialchars() for ANY kind of output, there is NO WAY someone would cross-side script in your page, as everything is printed verbatim. Of course when you need to process HTML tags things get a lot more complicated....

  4. #29
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ceefour: noone ever stated that quoting would prevent injection. But quoting ESCAPED variables of ALL types in EVERY query is probably 100% injection-proof.

  5. #30
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with the following fact: tweaking queries is almost inevitable (be it for LIMIT or the use of RegExp with LIKE) if you have to move to a new db.

    I still don't see why it makes the use of quoted int something good. It might sound like a poor argument, but it simply can't get any richer. Yes LIMIT is plain MySql-ish, yes we use LIMIT a lot. Is that to say that we can use quoted integers? Mmhh... I missed a logical link. But at the end of the day, each one its own.

    Good practice is obvious in this case: you will have to tweak more queries. On a project with many queries, that can become quite a pain.


  6. #31
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To explain why I think quoting is just as good as variable verification;

    Let's say we have a query
    SELECT * FROM article WHERE article_id = %article_id%
    where article_id is obviously an int field.

    Let's suppose that article_id comes in this script as a part of a request. Now imagine a script-kiddie that tries to hack into it by posting article_id='5; DELETE * FROM article;', (without the quotes).

    Now we have these scenarios:
    1) No type verification, no casting, no escaping, no quoting
    Query: SELECT * FROM article WHERE article_id = 5; DELETE * FROM article;
    Risk: all fields

    2) Just escaping
    Query: SELECT * FROM article WHERE article_id = 5; DELETE * FROM article;
    Same as (1), sincer no character is escaped.
    Risk: all non-quoted fields (ints, floats, ...)

    3) Escaping and type verification
    Verify the incoming parameters with is_int() and such.
    Script refuses to run the query, since the parameter is not an integer.
    Risk: none
    Pros: more cross-db compatible, DB isn't bothered by invalid queries
    Cons: Have to type-check every incoming variable, that will not be quoted in the query

    4) Escaping and quoting
    Query: SELECT * FROM article WHERE article_id = '5; DELETE * FROM article;'
    Query fails on execution.
    Risk: none
    Pros: Easy and unified use; every variable gets escaped and quoted, no exeptions.
    Cons: Presumably not compatible with DBs other than mysql/postgre (dunno really). Also the DB has to execute the query, even though the script could already stop execution earlier.

    By escaping, I mean the proper escaping, depending on a database (NOT addslashes). I use ADODB, which (as probably every DB abstraction layer does) has a function to escape a variable, using the specific syntax of the db in use.

    Neither (3) nor (4) have that much of advantage to dump the other on account of some prejudice. (4) is easier to use, (3) is more strict, but both disable any user-input injection. Also, example (2) makes it very clear: just escaping the incoming variables is not even remotely secure.

    Regards.

  7. #32
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very interesting and instructive post. You won't make me use quoted int but... I must admit that your ways definitly make sense.


  8. #33
    SitePoint Zealot bronze trophy
    Join Date
    Jun 2004
    Location
    Stockholm, Sweden
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What happens if the user posts something like:
    Code:
    5'; DELETE * FROM article;''
    Wouldn't 4) turn into:
    Code:
    SELECT * FROM article WHERE article_id = '5'; DELETE * FROM article;''
    Or are ' among the characters that are escaped?
    If there is a way to overcome the suffering, there is no need to worry; if there is no way to overcome the suffering, there is no point to worry.
    - Shantideva

  9. #34
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dbevfat
    But quoting ESCAPED variables of ALL types in EVERY query is probably 100% injection-proof.
    Unless you're using LIKE and similar feautures with its own metachars, for example:

    DELETE FROM tbl WHERE name LIKE '<user-input>%'

    Just escaping <user-input> doesn't give enough protection

    Quote Originally Posted by dbevfat
    3) Escaping and type verification
    Verify the incoming parameters with is_int() and such.
    Script refuses to run the query, since the parameter is not an integer.
    The most powerful and safe method you've forgotten is to force type instead of checking it:

    sprintf("SELECT * FROM article WHERE article_id = %d", $iser_input);

    solves the problem.

  10. #35
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by KTottE
    ...
    Or are ' among the characters that are escaped?
    Depends on the database; it's escaping and quoting function. In Mysql this query would be
    SELECT * FROM article WHERE article_id = '5\'; DELETE * FROM article;\'\''
    since the ' is quoting character and is escaped.

    I think in some databases (not sure, but possibly Interbase-based (includes Firebird)) the quoting character is also ', but escaping is done with another ' added in front of it, resulting in
    SELECT * FROM article WHERE article_id = '5''; DELETE * FROM article;'''''
    (all quotes are single)

    And some (MSSQL maybe?) use " as quoting character, so ' doesn't have to be escaped, the query is:
    SELECT * FROM article WHERE article_id = "5'; DELETE * FROM article;''"
    (first and last quotes double, other single)

    All goes well if you use proper (that is, database-specific) functions: for escaping and quoting.

    I am not really aware of which quoting/escaping syntax is used by other databases other than MySQL and PostgreSQL, so the speculations about Interbase and MSSQL are just that - speculations.

    stereofrog:
    In your case (remember that request variables are always strings), the possible situations are as follows:
    - $user_input is a string containing an integer, i.e. '1324' => properly converted
    - $user_input is a string _beginning_ with an integer, i.e. '12x34' => converted to 12
    - $user_input is a string _not_ beginning with an int, i.e. 'abc 243' => converted to 0
    All resulting in a valid (erroless) query, but with possibly unwanted behaviour, since the query is actually executed, it does not fail. This would result at least in retrieval of wrong data (or no data at all), but no database error.

    Regards.

  11. #36
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I feel I'm missing the point here. Are we talking about SQL injection, input validation or runtime typechecks? These are different things and it would be unwise to confuse them.

  12. #37
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dbevfat
    Unless you use some query builder db-independant layer (anyone knows any good one?).
    Again, try this
    SafeSQL class
    "SafeSQL - an SQL query processer to automate the tedious tasks of syntax
    testing, injection attack-proofing, dropping parts of queries and other
    misc features. It has only been tested with MySQL syntax, but any ANSI
    SQL-92 compliant db library should work OK."

    Also have a look at this sitepoint article for more examples of SQL Injection Attacks and you will see that merely quoting ints is not going to do it.

  13. #38
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    NH
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm a little confused over this whole subject. According to Harry Fuecks:

    http://www.webmasterstop.com/63.html

    Once the variable (with addslashes) has been entered into the db, the extra slashes (or escaped values) are discarded. So, I just set up a db to test this and sure enough, all my escaped strings were entered into the db without the "/".

    So, as this is the case, why would you ever need to use strip_slashes() as there are no slashes to be stripped from the data?

    Also, when you are adding addslashes to a variable to add the data to the db, which way is the correct way:

    1. $sql = "insert into...blah... addslashes($myvar)";

    or

    2. $sql = "insert .......";
    $sql = addslashes($sql);

    Thanks for the help and insight!

    Rob.

  14. #39
    SitePoint Zealot bronze trophy
    Join Date
    Jun 2004
    Location
    Stockholm, Sweden
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gotcha dbevfat.

    @stereofrog:
    SQL injection is a result of not validating input and/or not doing runtime type checks. Although in this case runtime type checks are part of the validation process. If we're supposed to get only integers, we make sure (validate) that this is the case with runtime type checks.
    If there is a way to overcome the suffering, there is no need to worry; if there is no way to overcome the suffering, there is no point to worry.
    - Shantideva

  15. #40
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 1skydive
    I'm a little confused over this whole subject. According to Harry Fuecks:

    http://www.webmasterstop.com/63.html

    Once the variable (with addslashes) has been entered into the db, the extra slashes (or escaped values) are discarded. So, I just set up a db to test this and sure enough, all my escaped strings were entered into the db without the "/".

    So, as this is the case, why would you ever need to use strip_slashes() as there are no slashes to be stripped from the data?

    Also, when you are adding addslashes to a variable to add the data to the db, which way is the correct way:

    1. $sql = "insert into...blah... addslashes($myvar)";

    or

    2. $sql = "insert .......";
    $sql = addslashes($sql);

    Thanks for the help and insight!

    Rob.
    add slashes only to the variable you want to clean (1 is the better solution, but it's even better to use the function outside your query for legibility issues).

    strip_slashes() has been created to counter the effect of having magic_quotes_gpc() turned on. Two slashes would be added if you used addslahes() as with magic_quotes_gpc() turned on. With strip_slashes, you can virtually disable magic_quotes_gpc(), thus working in any environment:

    PHP Code:
    function strip_magic_quotes($arr)
    {
        foreach (
    $arr as $k => $v)
        {
            if (
    is_array($v))
                { 
    $arr[$k] = strip_magic_quotes($v); }
            else
                { 
    $arr[$k] = stripslashes($v); }
        }

        return 
    $arr;
    }

    if (
    get_magic_quotes_gpc())
    {
        if (!empty(
    $_GET))    { $_GET    strip_magic_quotes($_GET);    }
        if (!empty(
    $_POST))   { $_POST   strip_magic_quotes($_POST);   }
        if (!empty(
    $_COOKIE)) { $_COOKIE strip_magic_quotes($_COOKIE); }


  16. #41
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by KTottE
    SQL injection is a result of not validating input and/or not doing runtime type checks.
    I have to disargee. Injection problem has absolutely nothing to do with validation. That is, 'valid' data can cause an injection, and vice versa.

    Classical example:

    DELETE FROM company WHERE name = '<input>'

    <input> equal to XYZ' OR '1 is obviously an "injection", but it should pass all validation test, because it seems to be a valid Company Name (we need some kind of semantic analysis to prove the contrary).

  17. #42
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by stereofrog
    I have to disargee. Injection problem has absolutely nothing to do with validation. That is, 'valid' data can cause an injection, and vice versa.

    Classical example:

    DELETE FROM company WHERE name = '<input>'

    <input> equal to XYZ' OR '1 is obviously an "injection", but it should pass all validation test, because it seems to be a valid Company Name (we need some kind of semantic analysis to prove the contrary).
    mmmh... good point, kinda cutting-edge but interesting.

  18. #43
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    NH
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by duuudie
    add slashes only to the variable you want to clean (1 is the better solution, but it's even better to use the function outside your query for legibility issues).

    strip_slashes() has been created to counter the effect of having magic_quotes_gpc() turned on. Two slashes would be added if you used addslahes() as with magic_quotes_gpc() turned on. With strip_slashes, you can virtually disable magic_quotes_gpc(), thus working in any environment:
    Ahh... I see. So as long as I have magic_quotes off and then addslashes() to my variables, I shouldn't have to use strip_slashes() ever again.

    So, other than the other things related to sql injection discussed in this thread, I should be ok with this setup?

    Thanks again.

    Rob.

  19. #44
    SitePoint Evangelist CapitalWebHost's Avatar
    Join Date
    Apr 2003
    Location
    Albany, NY
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use this function on all vars being written to a database..

    Code:
    function smart_quote($var)
    	{
       		// Stripslashes
    	   if (get_magic_quotes_gpc()) {
        	   $var = stripslashes($var);
    	   }
       		// Quote if not integer
       		if (!is_numeric($var)) {
           	$var = "'" . mysql_real_escape_string($var) . "'";
       		}
       		return $var;
    }

  20. #45
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Roger Ramjet: Thanks, I must've overlooked your hint before.

    stereofrog: For string variables, type-checking isn't gonna do any good, since any incoming variable is obviously a valid string. That's why these must be escaped, and you can't inject anything through a variable that has been (checked and escaped) or (escaped and quoted). The escaping is always essential, other checks vary from person to person, by their prefered methods, but just escaping or just quoting or just checking is simply not enough.

    CapitalWebHost: what about variables that were NOT a part of the request and have backslashes in them? If magic_quotes_gpc is on, these will also be stripped. Unless you keep track of which variable came in the script via request and only escape these when building query. I deal with this with the method that duuudie wrote a few posts above - all incoming variables are stripped if necessary. Then you KNOW that every variable you're dealing in the script doesn't have backslashes and you have to escape them before posting to db.

    1skydive: addslashes() is good for some databases, but others don't use backslash to escape certain characters. For mysql, it's better to use mysql_real_escape_string().

    Regards

  21. #46
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For apache users you can use mod_security and use some rules to protect against sql injection.

    http://www.webhostgear.com/62.html

    I installed it today, broke some scripts but after viewing the audit log was back in in seconds.

    Silly

  22. #47
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 1skydive
    Ahh... I see. So as long as I have magic_quotes off and then addslashes() to my variables, I shouldn't have to use strip_slashes() ever again.

    So, other than the other things related to sql injection discussed in this thread, I should be ok with this setup?

    Thanks again.

    Rob.
    yes, just use mysql_real_escape_string() (as stated above) instead of addslashes. Just note that using the code posted above (includeing it at the top of your pages dealing with gpc) will virtually disable magic_quotes_gpc. It's not ressources intensive.


  23. #48
    SitePoint Evangelist CapitalWebHost's Avatar
    Join Date
    Apr 2003
    Location
    Albany, NY
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dbevfat
    Roger Ramjet: Thanks, I must've overlooked your hint before.

    CapitalWebHost: what about variables that were NOT a part of the request and have backslashes in them? If magic_quotes_gpc is on, these will also be stripped. Unless you keep track of which variable came in the script via request and only escape these when building query. I deal with this with the method that duuudie wrote a few posts above - all incoming variables are stripped if necessary. Then you KNOW that every variable you're dealing in the script doesn't have backslashes and you have to escape them before posting to db.

    Regards
    So you're saying I should put all incoming vars through this:

    Code:
    function strip_magic_quotes($arr) 
    { 
        foreach ($arr as $k => $v) 
        { 
            if (is_array($v)) 
                { $arr[$k] = strip_magic_quotes($v); } 
            else 
                { $arr[$k] = stripslashes($v); } 
        } 
    
        return $arr; 
    } 
    
    if (get_magic_quotes_gpc()) 
    { 
        if (!empty($_GET))    { $_GET    = strip_magic_quotes($_GET);    } 
        if (!empty($_POST))   { $_POST   = strip_magic_quotes($_POST);   } 
        if (!empty($_COOKIE)) { $_COOKIE = strip_magic_quotes($_COOKIE); } 
    }
    and then addslash any that are being used to build a query?

    Hmm..makes sense...extra work..lol..but makes sense.

  24. #49
    SitePoint Zealot ceefour's Avatar
    Join Date
    Feb 2005
    Location
    Bandung, Indonesia
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wish when I wake up tomorrow magic_quotes_* and addslashes() will magically vanish from this world so nobody will have to be confused why they ever existed in the first place. They're evil. They should die. Escaping (and by all means also quoting) is strictly a database-dependent and context-dependent (like escaping inside a REGEXP or LIKE pattern) issue, and if these "magic" stuff aren't that intelligently magic they merely just add to the confusion and do no good.

    I wonder why PHP developers didn't also add magic_htmlspecialchars to prevent cross-site scripting? And also magic_preg_quote to prevent that nasty preg '/e' modifier injection that got popular in recently old phpBB scripts? And also magic_urlencode? I guess PHP developers aren't too creative IMHO in the "magical" sense. ;-)

  25. #50
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CapitalWebHost: almost, but DON'T use addslashes. Use mysql_real_escape_string() if you're using MySQL.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •