SitePoint Sponsor

User Tag List

Page 2 of 4 FirstFirst 1234 LastLast
Results 26 to 50 of 81
  1. #26
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is not a part of the ANSI SQL standard, hence it is non-standard. So is LIMIT and a bunch of other MySQL proprietary features. The trade-off is, of course, that with LIMIT you gain some functionality at the expense of portability.

    With PASSWORD, though, you really gain nothing other than an increased dependancy on MySQL. It provides a proprietary method for one-way hashing of a password.

    A standard function, availible in virtually every programming language and library for one-way hashing is MD5. It is mathematically secure and standardized (meaning MD5 in one language is the same as MD5 in another). Further you can even perform MD5 in SQL in case both your RDBMS and programming language didn't support MD5.

    Nothing is stopping MySQL from changing the way PASSWORD functions. Perhaps they change the amount of characters the password function outputs. Perhaps they change the hash function so that old hashes no longer match. There's no guarantee that will not happen with MD5, although since it is standardized the liklihood of that happening is far, far less.

    Anyway, when you run array_map( 'addslashes', $_POST ); you are securing your SQL against string-based attacks. The issue is not 'crap' as you stated earlier; we have seen how it can be used to elevate user privlidges. Further it 'not working' in certain cases as again you have illustrated is not that there is not a problem, but that you already provided a fix for it, which is all the original author was trying to get you to do. It's like people saying "Buckle your seatbelt so you don't die in an accident" and you getting in an accident and saying "Hey I didn't die, what are you talking about?" when you were ALREADY wearing your seatbelt. People who don't buckle up have a much higher risk of being severely injured or killed in a car accident. To those who wear their seatbelts: "Super, just remember to wear them every time!". Similarly, SQL Injection is very real and can happen if one forgets to sanatize their user-submitted variables. However, if you sanatize them, then you won't have a problem, case closed, life is good, etc.

    However in your case, there is still the issue of integer-based methods since adding slashes will not preclude a comment character or extra SQL going through. So you would need to individually run the intval function on the $_POST members which are supposed to be numeric ONLY.

    Names of tables are very easy to deduce (virtually everyone uses 'user', etc.) and as your example showed if you leave your SQL queries unchecked MySQL will spit out an ugly error, typically with the SQL command echoed. That is the SECOND part to the SQL Injection Prevention -- do NOT allow your programming language to echo errors directly. Perform rudimentary error controls so that malicious users cannot gain knowledge of your data structures.

    If you are using parameters, such as your $uid, make sure they do not hint as to what the internal strutures are. When I visit show_user.php?uid=bobross it is fairly obvious that uid maps to something like uid, userid, etc.. Internal variables can and should be descriptive but POST/GET/etc. vars should not betray what your internal structures are.
    Last edited by MattR; Aug 16, 2002 at 22:09.

  2. #27
    SitePoint Guru
    Join Date
    Feb 2002
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes yes of course, i know about MD5, and i also know about everything else you said.
    But keep in mind that when developing i have error echoed, of course if done it is turned off etc...

    Course i know about VALUES needed to be checked in integers only, but sofar i had no need for that, as i don't have numerical data only (unless of course with math. operations.

    About guessing table names, did you ever hear about $prefix?

    If im ever worried about people attempting to do things like deleting my tables (which can be checked by logging errors that are spit out), i simply have the $prefix changed every day (if neccesarry even auto.,.

    For example

    $prefix = "TkUsLO45JK";

    my tables are now all named
    TkUsLO45JK_users or TkUsLO45JK_articles, have fun guessing around

  3. #28
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great, you're following all that the article stated as 'good practices'. So why the outcry about the article (or more precisely 'SQL Injection') being 'crap'?

    P.S. it is not necessary to be able to enter more than one command to erase a table. All the cracker needs is to find one vulnerable DELETE query and perform the same attack as we've shown before, e.g.:
    Code:
    DELETE FROM user
          WHERE username = '$username'
    Replace $username with something along the lines of:
    ' OR 1=1 #

    And it has now deleted every row in your table.
    Last edited by MattR; Aug 16, 2002 at 22:20.

  4. #29
    SitePoint Guru
    Join Date
    Feb 2002
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heh! Hold it matey...
    I never said the ARTICLE was crap, i said that tdarlings comment was crap, especially with the delete query, cause sofar i could not get it to work, and im trying hard, cause i like to know just how secure my code is.

    I only said that I THINK what is written in the article mostly applies to MS SQL (which i believe is where the original threat came from, could be wrong though).

    Also i don't believe that the stuff weve done now had anything to do with the original SQL Injection problem.

    Let's face it, you ask this any RDBMS Developer and he will laugh at us, he will say, it is up to the programmer to make sure incoming data is checked etc...

    Which was btw the response i got whilst speaking to someone who develops SAP applications (don't know if you know this, complicated to explain, i hardly know myself).

    Anyway, im still interested MattR if you could come up with a command that could delete one of my tables provided you know the name of course. If you have a working one share it please cause i would really like to see

    Cheers,
    datune

  5. #30
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, it is very very unproffesional to claim that MySQL is amateurish, this again proves that you didn't do your homework well.
    Or maybe I did my homework too well... What I say about MySQL is not a claim. It's a fact. The problem is that there are so many programmers out there who learn by books that teach bad stuff, or who don't really now what a database is... The result is that 'everybody uses it', so 'it must be good'. Crap. There are many Open Source DBMS's that are far better than MySQL. PostgreSQL is one. Sybase is another. And what about SAP-DB?

    Anyway, the cure is, as I posted before, to run your variables through addslashes and intval.
    Actually, addslashes is (just like PASSWORD) MySQL-specific and non-ANSI. I know of one other DBMS that supports it (PostgreSQL), but that doesn't make it right. The proper thing to do is to replace single quotes with two single quotes:

    PHP Code:
    $new str_replace("'""''"$old); 
    That is ANSI, so that is what you should use.

    About guessing table names, did you ever hear about $prefix?
    It sucks. It's not a solution to any problem; it's only working around your own incompetence: "I don't know ho secure my DBMS properly, so if I just give my tables weird names, the problem will go away by itself." Yeah right.

    Let's face it, you ask this any RDBMS Developer and he will laugh at us, he will say, it is up to the programmer to make sure incoming data is checked etc...
    No he wouldn't. A 'real' RDBMS developer adds triggers and functions to the database schema to make sure the database logic is preserverd, INSIDE the database. But then, MySQL doesn't support triggers. Or functions. Or anything a proper DBMS should support.

    Vincent

  6. #31
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's damn fast though

  7. #32
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's damn fast though
    Yes, but only if you use the default table types. And these are pretty useless, as they don't support any of the required DBMS properties. As soon as you need those (which is always), you must use the InnoDB table type, which isn't any faster than PostgreSQL (just to name one).

    Vincent

  8. #33
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Care to elaborate voostind? Not on the speed issue but the table type properties and why some are useless, and I suppose why you need them will then become self-explanatory.

    Simply curious...
    Oh no! the coots are eating my nodes!

  9. #34
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, there's a whole list, and I won't mention it here completely. But examples? Alright:

    - Views. These are mandatory for any DBMS. The MySQL developers say "they're not that important", which should already tell you something.
    - Cascades/Restricts. Although it is possible to write these in the database schema, MySQL simply ignores them. Say you have a user table with a primary key on 'login' and you refer to this table from another one. If the login is changed, all occurrences of the old name in the database must be changed to the new name. MySQL doesn't do this, so you either do it manually, or use a primary key that doesn't change (like an auto-incremented integer). The first is bad, because all database logic should be in the database and not in the (PHP) code. The second is also bad, because you don't want to change your perfectly fine database schema just because a particular piece of software doesn't know how to work with it.
    - Triggers and functions. Also required to maintain database logic inside the database itself.
    - Checks. You can write checks in the database schema, but they always return TRUE; so they are of no use.
    - Foreign keys. You can write them down, but MySQL ignores them.
    - Need I go on?

    Some things (like checks) can be enabled by using a different table type (InnoDB). But this comes at a price: loss of speed. In fact, if you use InnoDB, you might as well use PostgreSQL or Sybase (or whatever), because both systems are more robust, and also faster in that case.

    In short, if you have a database that consists of only one table; or of tables that aren't connected in any way, MySQL is fine. However, such a database doesn't exist...

    Vincent

  10. #35
    SitePoint Guru
    Join Date
    Feb 2002
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, voostind, i get the feeling that youre taking things a bit too personal, chill down it's only a discussion, after all that's what makes it interesting

    Look, even MattR has agreed to the fact (in another post, but nonetheless he did, that MySQL will be sufficient enough for small/medium loads. (that's his words im using, cause i don't have the experience that he has with DBMS, you can read up on the post http://sitepointforums.com/showthrea...5&pagenumber=2 if you want.)

    However, here comes you, saying that MySQL is of no good.
    Come one voostind, don't you think that you're excaggerating a tiny bit?

    No he wouldn't. A 'real' RDBMS developer adds triggers and functions to the database schema to make sure the database logic is preserverd, INSIDE the database. But then, MySQL doesn't support triggers. Or functions. Or anything a proper DBMS should support.
    Wrong, again.

    You see, a good developer will ALWAYS use what is best and appropiate for your application.
    Any application being seriously developed will have a "Product requirement specifications" booklet, and the RDBMS Developer will use that to judge which DBMS to use.
    At least that's how i have experienced it sofar.

    And you know, sofar i have never met any developer in the real world saying "Oh no, MySQL is crap, you should only use "put here what you want", etc...". You do that in the real world and people will think of you as being unproffesional.

    Oh sure, doing an article database with automated tracking of stock for a huge supermarket with 150 shops divided all over the place you're not gonna use MySQL, but that's not the point is it.

    Actually, addslashes is (just like PASSWORD) MySQL-specific and non-ANSI. I know of one other DBMS that supports it (PostgreSQL), but that doesn't make it right. The proper thing to do is to replace single quotes with two single quotes:


    PHP:--------------------------------------------------------------------------------
    $new = str_replace("'", "''", $old);

    --------------------------------------------------------------------------------


    That is ANSI, so that is what you should use.
    That's some very interresting information, and i admit it is the first time ever i hear such an approach, and if it is going to work, im sure going to use it.

    It sucks. It's not a solution to any problem; it's only working around your own incompetence: "I don't know ho secure my DBMS properly, so if I just give my tables weird names, the problem will go away by itself." Yeah right.
    It's actually just an added security bonus, nothing else matey. If you think about it you will agree (unless youre being stubborn of course).
    I never said it is an solution for everything, but it's nice to use.
    Oh, and you can be quite sure that with a real hacker you don't stand a chance, he is not going to try and delete one table of yours...he's gonna delete your whole application.

    Have a nice day!
    datune

  11. #36
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you voostind for your answer(s)!

    I'm still in the mindset that I don't "need" those things just yet but it is nonetheless helpful to know all those features of databases in relation to databases as a whole.

    The big issue for me is that it is much easier to get hosting with MySQL databases (and cheaper) than it is to get hosting with other database support. I do envisage switching to a real RDBMS in the future however it is not a priority at this point in time because MySQL does the job I want it to. - although I can see how MySQL has plenty of room for improvement.

    Datune don't tell voostind he's wrong just because you don't agree with him. What you see as a good developer/database user and what he sees as one is a subjective point.
    Oh no! the coots are eating my nodes!

  12. #37
    Anonymous
    SitePoint Community Guest
    Great article.
    Thanks!

  13. #38
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In defense of Datatune all he said was that tdarlings example was wrong/crap and it was , that particular attack will not work in MySQL fullstop whatever variances you apply.

    That MySQL is vunerable to SQL injection is neither new or interesting , though its good to keep the subject popping up now and again.

    That MySQL is not a true RDBMS is neither new nor interesting , that it ably powers millions of sites on ridiculously overloaded shared servers is a fact you can ignore at leisure.

    I hear time and again from peeps in these forums 'use the right tool for the job' , MySQL is the right tool for many many jobs , & just because someone uses it does not diminish thier IQ.

    I have just started playing with sqlite and (even though it supports triggers & views etc it is obviously not an RDBMS , its a glorified flatfile , but it too has its uses in specific places.

    Would you have us all use PostgreSQL or Oracle if we are not to be branded something 'orrible

    when posgreSQL finally gets x-platform then it may be useful to the masses (I started my web-development on postgreSQL & eek ! mSQL) as I see WAMP users get laughed out the court as well...

    beginning to sound a bit l33t to me ...

  14. #39
    Anonymous
    SitePoint Community Guest
    The examples provided are useful but it's not practical to think that any developer of a big corporate site would still do stuff like controlling username/passwords and related user rights in the database instead of the page logic. If a developer cannot think that this is problematic while creating a connection in the first hand, should resign.

  15. #40
    Anonymous
    SitePoint Community Guest
    Good and very informative article, I had never heard about it before. Thanks for the informationa and samples

  16. #41
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just an interesting note, in regards to the use of the PASSWORD function:

    Quote Originally Posted by datune
    Now that doesn't make sense.
    If programming something specifically and knowing it's only going to be used on MySQL, why not use it? Being non-standard (as you claim it to be, although i know so many programmers using it, but anyway) it's an added plus.
    Anyone you know that *is* using the password function should stop:

    Quote Originally Posted by MySQL Manual
    Note: The PASSWORD() function is used by the authentication system in MySQL Server, you should NOT use it in your own applications. For that purpose, use MD5() or SHA1() instead. Also see RFC-2195 for more information about handling passwords and authentication securely in your application.
    (See http://www.mysql.com/doc/en/Miscella...functions.html)


    The only valid use for PASSWORD would be in a program like PHPMyAdmin, to manage MySQL
    My name is Steve, and I'm a super-villian.

  17. #42
    SitePoint Zealot
    Join Date
    Mar 2001
    Location
    North Wales, UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The manual doesn't actually say why you shouldn't use PASSWORD() in your own applications. Is there any reason apart from the lack of portability?

  18. #43
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It doesn't look like it uses MD5, so it isn't as secure, either. In short, there's no good reason to use it, so don't.

  19. #44
    This is all Mumbo Jumbo Amit's Avatar
    Join Date
    Oct 2001
    Location
    New Delhi, India
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, to take a few steps back. I would advise not allowing the special charaters (like ;,<> etc) in the username would actually prevent (in certain cases) the sql attack

    You can use something like this in JavaScript
    Code:
    function IsValid( oField )
    {
    	if (oField.name=='username')
    	{
    		re = /^(([a-z])+([a-z]|[0-9]|\_)*)*$/i;
    		$sMsg = "Username is invalid.\nUsername should contain only alphabets\nnumericals(0-9) and underscore(_)\nand should start with an alphabet."
    	}
    	if ( !re.test(oField.value) )
    	{
    		alert( $sMsg );
    		oField.value = '';
    		oField.focus();
    		return false;
    	}
    }
    And while using in the form ....
    Code:
    <input class='tb' type='text' name='username' onblur='IsValid(this);' maxlength='20' />
    And then again we have to check while processing the form (because if JavaScript is turned off, the above example would fail)

    PHP Code:
    $sErrMsg .= ( empty($username) ? "Username is empty.\n" '' )
        . ( !
    preg_match('/^(([a-z])+([a-z]|[0-9]|\_)*)*$/i'$username) ? "Username is not valid.\n" '' ); 
    and using the $sErrMsg later on ...

    PHP Code:
    if (!empty($sErrMsg))
    {
       echo 
    nl2br($sErrMsg);

    Also, if you want to restrict the username's character you can use the regular expression like this ....
    '/^(([a-z])+([a-z]|[0-9]|\_){4,19})?$/i'

    This would actually restrict the username from 5 to 20 characters.

    Similarly can be used for the other fields like password, etc ...

    Hope this helps ...

  20. #45
    SitePoint Enthusiast HogMan's Avatar
    Join Date
    Mar 2003
    Location
    Nebraska
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $new = str_replace("'", "''", $old);
    Does anyone use this instead of addslashes? From the php manual, addslashes does the following

    string addslashes(string str)
    Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).

    So, if not using addslashes with MySQL and just replacing the ' with '' (2 single quotes), won't that cause an issue if the query contains a " or \

    Some code that uses addslashes and str_replace on a name
    Code:
    #!/usr/bin/php -q
    <?php
    $name = "test'sare\\\"good";
    $slashname = addslashes($name);
    $othername = str_replace("'", "''", $name);
    echo "INSERT INTO foo (name) VALUES ('$slashname');\n";
    echo "INSERT INTO foo (name) VALUES ('$othername');\n";
    ?>
    Result queries
    Code:
    INSERT INTO foo (name) VALUES ('test\'sare\\\"good');
    INSERT INTO foo (name) VALUES ('test''sare\"good');
    Now, checking whats in the db
    Code:
    mysql> SELECT name FROM foo;
    +-----------------+
    | name            |
    +-----------------+
    | test'sare\"good |
    | test'sare"good  |
    +-----------------+
    With just replacing ' with '', won't there be an issue now? I would need to slash my string after getting it from the db where before I wouldn't? Sorry if I misunderstood this.

  21. #46
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The MySQL method of escaping strings (with the backslash) is non-standard. The SQL standard requires strings to be enclosed in single quotes (e.g. WHERE username = 'Bob Ross') and escaped with double-single quotes (e.g. WHERE store_name = 'Bob''s Bait Shop').

  22. #47
    This is all Mumbo Jumbo Amit's Avatar
    Join Date
    Oct 2001
    Location
    New Delhi, India
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would advise using mysql_escape_string() if want to escape the string for MySQL data.

  23. #48
    SitePoint Enthusiast HogMan's Avatar
    Join Date
    Mar 2003
    Location
    Nebraska
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    The MySQL method of escaping strings (with the backslash) is non-standard. The SQL standard requires strings to be enclosed in single quotes (e.g. WHERE username = 'Bob Ross') and escaped with double-single quotes (e.g. WHERE store_name = 'Bob''s Bait Shop').
    Sorry, I'm confused (which is common for me ). So to follow the standard and not use addslashes, wouldn't we still need to worry about \ and " so they are escaped properly?

  24. #49
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you use the string replace function as provided (and follow the standard for strings) everything will work fine in most DBMSs. I don't know about MySQL.

  25. #50
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    One thing I have not seen mentioned here: Permissions.

    Alot of these issue can be handled very efficently by making sure your webuser db account cannot do things they dont need to do. Like DROP tables.

    When I work with SQL server my web user can't even access tables directly. Instead I make stored procedures and views as needed to allow them to read and update data.

    WWB


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
  •