SitePoint Sponsor |
|
User Tag List
Results 26 to 50 of 81
-
Aug 16, 2002, 22:04 #26
- 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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Aug 16, 2002, 22:13 #27
- 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
-
Aug 16, 2002, 22:15 #28
- 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'
' OR 1=1 #
And it has now deleted every row in your table.Last edited by MattR; Aug 16, 2002 at 22:20.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Aug 16, 2002, 22:25 #29
- 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
-
Aug 16, 2002, 23:42 #30
- 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.
Anyway, the cure is, as I posted before, to run your variables through addslashes and intval.
PHP Code:$new = str_replace("'", "''", $old);
About guessing table names, did you ever hear about $prefix?
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...
Vincent
-
Aug 17, 2002, 02:13 #31
- Join Date
- Jan 2001
- Location
- Lawrence, Kansas
- Posts
- 2,066
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
It's damn fast though
-
Aug 17, 2002, 02:24 #32
- Join Date
- Oct 2001
- Posts
- 592
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
It's damn fast though
Vincent
-
Aug 17, 2002, 04:11 #33
- 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!
-
Aug 17, 2002, 05:12 #34
- 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
-
Aug 17, 2002, 05:51 #35
- 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.
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.
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.
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
-
Aug 17, 2002, 15:24 #36
- 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!
-
Jul 17, 2003, 14:48 #37AnonymousSitePoint Community Guest
Great article.
Thanks!
-
Jul 17, 2003, 20:01 #38
- 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 ...
-
Aug 8, 2003, 02:10 #39AnonymousSitePoint 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.
-
Aug 13, 2003, 02:08 #40AnonymousSitePoint Community Guest
Good and very informative article, I had never heard about it before. Thanks for the informationa and samples
-
Aug 13, 2003, 07:44 #41
- 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:
Originally Posted by datune
Originally Posted by MySQL Manual
The only valid use for PASSWORD would be in a program like PHPMyAdmin, to manage MySQLMy name is Steve, and I'm a super-villian.
-
Aug 14, 2003, 05:14 #42
- 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?
-
Aug 14, 2003, 05:17 #43
- 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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Aug 14, 2003, 06:27 #44
- 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; } }
Code:<input class='tb' type='text' name='username' onblur='IsValid(this);' maxlength='20' />
PHP Code:$sErrMsg .= ( empty($username) ? "Username is empty.\n" : '' )
. ( !preg_match('/^(([a-z])+([a-z]|[0-9]|\_)*)*$/i', $username) ? "Username is not valid.\n" : '' );
PHP Code:if (!empty($sErrMsg))
{
echo nl2br($sErrMsg);
}
'/^(([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 ...
-
Aug 14, 2003, 07:37 #45
- Join Date
- Mar 2003
- Location
- Nebraska
- Posts
- 86
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
$new = str_replace("'", "''", $old);
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"; ?>
Code:INSERT INTO foo (name) VALUES ('test\'sare\\\"good'); INSERT INTO foo (name) VALUES ('test''sare\"good');
Code:mysql> SELECT name FROM foo; +-----------------+ | name | +-----------------+ | test'sare\"good | | test'sare"good | +-----------------+
-
Aug 14, 2003, 07:40 #46
- 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').
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Aug 14, 2003, 07:44 #47
- 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.
-
Aug 14, 2003, 08:11 #48
- Join Date
- Mar 2003
- Location
- Nebraska
- Posts
- 86
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by MattR
). So to follow the standard and not use addslashes, wouldn't we still need to worry about \ and " so they are escaped properly?
-
Aug 14, 2003, 10:46 #49
- 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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Aug 14, 2003, 12:02 #50
- Join Date
- May 2003
- Location
- Washington, DC
- Posts
- 10,653
- 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