RE: [PHP] basic user/input form questions... more validation!
chris... i would agree, and didn't think it made sense.. but i don't know what you mean by the phrase 'escape all output'!! i don't see the need to escape all output from the mysql db/tbl... so i'm not sure you meant this. for my $0.02, i'd quote/escape all 'strings/date' vars that would be inserted/used within the mysql db. i'd simply go ahead and insert numeric data with no quotes. i'd thoroughly verify/validate using regex or other methods all data before using it in the sql commands. as fars as getting data from the mysql db/tbl, i'd simply use the sql command/query. i'd extract the resulting data, and use the data in vars that i've defined to be the specific data type. this allows me to farily consistenly know what data types i'm using, and how to then present the data to the user if i have to, as well as how to use the vars/data in other parts of the given application. -bruce -Original Message- From: Chris Shiflett [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 9:22 PM To: [EMAIL PROTECTED] Cc: php-general@lists.php.net Subject: Re: [PHP] basic user/input form questions... more validation! bruce wrote: my question was directed towards trying to understand if you were meaning that an app should escape all output from the mysql db? If you think about that for a moment, I think you'll see that it doesn't make a lot of sense. Data that you get from a remote source is input, not output. Data that you send to a remote source is output. Hope that helps. Chris -- Chris Shiflett Brain Bulb, The PHP Consultancy http://brainbulb.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] basic user/input form questions... more validation!
bruce wrote: i would agree, and didn't think it made sense.. but i don't know what you mean by the phrase 'escape all output'!! i don't see the need to escape all output from the mysql db/tbl... so i'm not sure you meant this. Output can be (among other things): * Your script = MySQL * Your script = User's browser * Your script = Shell Anything that leaves the realms of your PHP script is considered output from that script, and should be escaped. * Output from your script to MySQL should be escaped with mysql_real_escape_string() * Output from your script to the user's browser should be escaped with htmlspecialchars() * Output from your script to the shell should be escaped with escapeshellcmd() and escapeshellarg() [snip] as fars as getting data from the mysql db/tbl, i'd simply use the sql command/query. i'd extract the resulting data, and use the data in vars that i've defined to be the specific data type. And hopefully htmlspecialchars() it if you intend outputting it to the user's browser... -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
chris... i understood the concept of data being output from an application/function. my question was directed towards trying to understand if you were meaning that an app should escape all output from the mysql db?? or, were you referring to data that would go back to the user via a form? in other words, which 'output' function are/were you referring to. -bruce ps. tried to get to the link... it wouldn't come up for me for some reason... -Original Message- From: Chris Shiflett [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 8:38 PM To: [EMAIL PROTECTED] Cc: 'Chris W. Parker'; php-general@lists.php.net Subject: Re: [PHP] basic user/input form questions... more validation! bruce wrote: but what do you mean by ...escape output!! Output is data that you send somewhere else. In other words, if it leaves your application, it is output. This is explained a bit further (with some code) near the start of this talk: http://brainbulb.com/talks/php-security-audit-howto.pdf Hope that helps. Chris -- Chris Shiflett Brain Bulb, The PHP Consultancy http://brainbulb.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
You should be careful about column types in mysql especially if you are doing joins. For example: mysql create temporary table justsomeresearch(foo varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into justsomeresearch values(3); Query OK, 1 row affected (0.00 sec) mysql insert into justsomeresearch values('3'); Query OK, 1 row affected (0.00 sec) mysql select * from justsomeresearch; +--+ | foo | +--+ | 3| | 3| +--+ 2 rows in set (0.00 sec) mysql create temporary table justmoreresearch(bar varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into justmoreresearch values(3); Query OK, 1 row affected (0.00 sec) mysql select * from justmoreresearch, justsomeresearch where bar = foo; +--+--+ | bar | foo | +--+--+ | 3| 3| | 3| 3| +--+--+ 2 rows in set (0.00 sec) See this works because both the number version and the 'char' version are the same.. but let's do something else mysql update justsomeresearch set foo = '03' where foo=3; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql select * from justsomeresearch; +--+ | foo | +--+ | 03 | | 03 | +--+ 2 rows in set (0.00 sec) mysql select * from justmoreresearch, justsomeresearch where bar = foo; Empty set (0.00 sec) You see.. because the '03' is not the same as 3 it doesn't join, you would either have to have both columns as ints or make sure both columns were in the same format as a char. Now let's look at int column type mysql create temporary table evenmoreresearch(foo int(10)); Query OK, 0 rows affected (0.00 sec) mysql create temporary table andmoreresearch(foo int(10)); Query OK, 0 rows affected (0.00 sec) mysql create temporary table andmoreresearch(bar int(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into evenmoreresearch values(3); Query OK, 1 row affected (0.00 sec) mysql insert into evenmoreresearch values(03); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values(03); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values('3'); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values('03'); Query OK, 1 row affected (0.00 sec) mysql select * from evenmoreresearch, andmoreresearch where bar = foo; +--+--+ | foo | bar | +--+--+ |3 |3 | |3 |3 | |3 |3 | |3 |3 | |3 |3 | |3 |3 | +--+--+ 6 rows in set (0.00 sec) mysql select * from evenmoreresearch; +--+ | foo | +--+ |3 | |3 | +--+ 2 rows in set (0.00 sec) mysql select * from andmoreresearch; +--+ | bar | +--+ |3 | |3 | |3 | +--+ 3 rows in set (0.00 sec) You get some rather curious results. I've even switched the names around and it comes out with 6 results, exactly the same (or least as exactly as data to a php script would be). So if you're joining in mysql it's good to insert your data as ints into integer columns and 'char' or 'strings' when inserting into varchar, char or text columns (although why you would join text columns I have no idea). And this was only straight joins.. imagine what left right or other joins would look like. Carl Furst Vote.com P.O. Box 7 Georgetown, Ct 06829 203-544-8252 [EMAIL PROTECTED] -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 6:42 PM To: php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! bruce mailto:[EMAIL PROTECTED] on Thursday, September 22, 2005 3:33 PM said: further investigation seems to imply that 'strings' that are to be inserted into the mysql db should be 'backslashed' for the chars \x00, \n, \r, \,', and \x1a. That's what escaping is. the mysql_real_escape_string function requires a db connection and the app might not have opened up a connection to the db at this point in the code.. (or i could rewrite the code!!) Unless you have warnings print to the screen you should be fine. Or you could just suppress the errors on that one function. numeric data: -doesn't need quoting, but it shouldn't hurt to quote anyway.. (quote all numeric values inserted in the db...) -but wouldn't this require the app to detect numeric vals in the db, and to convert the 'type'!!) No. Why would it? If you quote everything then there's no need to check for type. -how does this affect date/float vars... I'm not sure. Check the MySQL manual on column types. extracting data from the db: numeric data -get the data/val from the db -check the type/convert the db to int/float/date/etc... No type conversion is necessary. PHP is a loose typed language. string data -get the vals from the db, -strip any slashes that were added to the data/vars -process/use accordingly... As I said in my previous email
RE: [PHP] basic user/input form questions... more validation!
which is why it's critical/important to really lay out (architect) your app and to think about how the app should be handling various data types. this also goes to thiking about how you name variables in your app. all of this is really software design 101 -bruce -Original Message- From: Carl Furst [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 9:48 AM To: 'Chris W. Parker'; php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! You should be careful about column types in mysql especially if you are doing joins. For example: mysql create temporary table justsomeresearch(foo varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into justsomeresearch values(3); Query OK, 1 row affected (0.00 sec) mysql insert into justsomeresearch values('3'); Query OK, 1 row affected (0.00 sec) mysql select * from justsomeresearch; +--+ | foo | +--+ | 3| | 3| +--+ 2 rows in set (0.00 sec) mysql create temporary table justmoreresearch(bar varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into justmoreresearch values(3); Query OK, 1 row affected (0.00 sec) mysql select * from justmoreresearch, justsomeresearch where bar = foo; +--+--+ | bar | foo | +--+--+ | 3| 3| | 3| 3| +--+--+ 2 rows in set (0.00 sec) See this works because both the number version and the 'char' version are the same.. but let's do something else mysql update justsomeresearch set foo = '03' where foo=3; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql select * from justsomeresearch; +--+ | foo | +--+ | 03 | | 03 | +--+ 2 rows in set (0.00 sec) mysql select * from justmoreresearch, justsomeresearch where bar = foo; Empty set (0.00 sec) You see.. because the '03' is not the same as 3 it doesn't join, you would either have to have both columns as ints or make sure both columns were in the same format as a char. Now let's look at int column type mysql create temporary table evenmoreresearch(foo int(10)); Query OK, 0 rows affected (0.00 sec) mysql create temporary table andmoreresearch(foo int(10)); Query OK, 0 rows affected (0.00 sec) mysql create temporary table andmoreresearch(bar int(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into evenmoreresearch values(3); Query OK, 1 row affected (0.00 sec) mysql insert into evenmoreresearch values(03); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values(03); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values('3'); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values('03'); Query OK, 1 row affected (0.00 sec) mysql select * from evenmoreresearch, andmoreresearch where bar = foo; +--+--+ | foo | bar | +--+--+ |3 |3 | |3 |3 | |3 |3 | |3 |3 | |3 |3 | |3 |3 | +--+--+ 6 rows in set (0.00 sec) mysql select * from evenmoreresearch; +--+ | foo | +--+ |3 | |3 | +--+ 2 rows in set (0.00 sec) mysql select * from andmoreresearch; +--+ | bar | +--+ |3 | |3 | |3 | +--+ 3 rows in set (0.00 sec) You get some rather curious results. I've even switched the names around and it comes out with 6 results, exactly the same (or least as exactly as data to a php script would be). So if you're joining in mysql it's good to insert your data as ints into integer columns and 'char' or 'strings' when inserting into varchar, char or text columns (although why you would join text columns I have no idea). And this was only straight joins.. imagine what left right or other joins would look like. Carl Furst Vote.com P.O. Box 7 Georgetown, Ct 06829 203-544-8252 [EMAIL PROTECTED] -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 6:42 PM To: php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! bruce mailto:[EMAIL PROTECTED] on Thursday, September 22, 2005 3:33 PM said: further investigation seems to imply that 'strings' that are to be inserted into the mysql db should be 'backslashed' for the chars \x00, \n, \r, \,', and \x1a. That's what escaping is. the mysql_real_escape_string function requires a db connection and the app might not have opened up a connection to the db at this point in the code.. (or i could rewrite the code!!) Unless you have warnings print to the screen you should be fine. Or you could just suppress the errors on that one function. numeric data: -doesn't need quoting, but it shouldn't hurt to quote anyway.. (quote all numeric values inserted in the db...) -but wouldn't this require the app to detect numeric vals in the db, and to convert the 'type'!!) No. Why would it? If you quote everything then there's no need to check
RE: [PHP] basic user/input form questions... more validation!
bruce mailto:[EMAIL PROTECTED] on Friday, September 23, 2005 10:46 AM said: which is why it's critical/important to really lay out (architect) your app and to think about how the app should be handling various data types. this also goes to thiking about how you name variables in your app. all of this is really software design 101 Oh whatever, I don't quote everything in my own apps anyway. You just seem to be so confused about this whole thing* that it'd be easier for you to just quote everything and escape everything and run everything through htmlspecialchars(). Chris. * Not that I'm not completely confused about other subjects myself. And doesn't anyone know how to trim anymore? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
That may be true, but the point about php being loosely typed is valid.. Although there are cast functions in php, you can store integers in strings and vice versa.. and move them around as much as you want... When you declare a variable it has no type and is not associated with any class per se, AND you can do most of the things you were talking about like putting quotes around all your input into the database. I was just saying that if you want to do joins you need to be more careful and match your data with the column type so you should quote data going into a varchar field and not quote numbers going into an int field and vice versa otherwise your joins are going to get screwed up and that's not something they teach you in software 101... Peace, Carl Furst Vote.com P.O. Box 7 Georgetown, Ct 06829 203-544-8252 [EMAIL PROTECTED] -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 1:46 PM To: 'Carl Furst'; 'Chris W. Parker'; php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! which is why it's critical/important to really lay out (architect) your app and to think about how the app should be handling various data types. this also goes to thiking about how you name variables in your app. all of this is really software design 101 -bruce -Original Message- From: Carl Furst [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 9:48 AM To: 'Chris W. Parker'; php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! You should be careful about column types in mysql especially if you are doing joins. For example: mysql create temporary table justsomeresearch(foo varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into justsomeresearch values(3); Query OK, 1 row affected (0.00 sec) mysql insert into justsomeresearch values('3'); Query OK, 1 row affected (0.00 sec) mysql select * from justsomeresearch; +--+ | foo | +--+ | 3| | 3| +--+ 2 rows in set (0.00 sec) mysql create temporary table justmoreresearch(bar varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into justmoreresearch values(3); Query OK, 1 row affected (0.00 sec) mysql select * from justmoreresearch, justsomeresearch where bar = foo; +--+--+ | bar | foo | +--+--+ | 3| 3| | 3| 3| +--+--+ 2 rows in set (0.00 sec) See this works because both the number version and the 'char' version are the same.. but let's do something else mysql update justsomeresearch set foo = '03' where foo=3; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql select * from justsomeresearch; +--+ | foo | +--+ | 03 | | 03 | +--+ 2 rows in set (0.00 sec) mysql select * from justmoreresearch, justsomeresearch where bar = foo; Empty set (0.00 sec) You see.. because the '03' is not the same as 3 it doesn't join, you would either have to have both columns as ints or make sure both columns were in the same format as a char. Now let's look at int column type mysql create temporary table evenmoreresearch(foo int(10)); Query OK, 0 rows affected (0.00 sec) mysql create temporary table andmoreresearch(foo int(10)); Query OK, 0 rows affected (0.00 sec) mysql create temporary table andmoreresearch(bar int(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into evenmoreresearch values(3); Query OK, 1 row affected (0.00 sec) mysql insert into evenmoreresearch values(03); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values(03); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values('3'); Query OK, 1 row affected (0.00 sec) mysql insert into andmoreresearch values('03'); Query OK, 1 row affected (0.00 sec) mysql select * from evenmoreresearch, andmoreresearch where bar = foo; +--+--+ | foo | bar | +--+--+ |3 |3 | |3 |3 | |3 |3 | |3 |3 | |3 |3 | |3 |3 | +--+--+ 6 rows in set (0.00 sec) mysql select * from evenmoreresearch; +--+ | foo | +--+ |3 | |3 | +--+ 2 rows in set (0.00 sec) mysql select * from andmoreresearch; +--+ | bar | +--+ |3 | |3 | |3 | +--+ 3 rows in set (0.00 sec) You get some rather curious results. I've even switched the names around and it comes out with 6 results, exactly the same (or least as exactly as data to a php script would be). So if you're joining in mysql it's good to insert your data as ints into integer columns and 'char' or 'strings' when inserting into varchar, char or text columns (although why you would join text columns I have no idea). And this was only straight joins.. imagine what left right or other joins would look like
RE: [PHP] basic user/input form questions... more validation!
bruce mailto:[EMAIL PROTECTED] on Thursday, September 22, 2005 4:19 PM said: the articles i've seen imply that if you addslashes, you also need to stripslashes on the backend... That's probably because gpc_magic_quotes (I think that's what it's called) is turned on and doing addslashes will double escape everything leaving you with a \ in the db. No escaping: Hello, I'm... Result after db insertion: Error, cannot insert gpc_magic_quotes: Hello, I\'m... Result after db insertion: Hello, I'm... gpc_magic_quotes + addslahes: Hello, I\\\'m... Result after db insertion: Hello, I\'m... So when you retrieve the data you would indeed have to do stripslashes() because escapging is being done wrong. With distributed apps it's a good practice to determine whether or not gpc_magic_quotes is turned on and then act accordingly. I don't know if mysql_real_escape_string() is subject to over escaping or not. You'd have to test it. Hth, Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] basic user/input form questions... more validation!
bruce wrote: my question was directed towards trying to understand if you were meaning that an app should escape all output from the mysql db? If you think about that for a moment, I think you'll see that it doesn't make a lot of sense. Data that you get from a remote source is input, not output. Data that you send to a remote source is output. Hope that helps. Chris -- Chris Shiflett Brain Bulb, The PHP Consultancy http://brainbulb.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] basic user/input form questions... more validation!
- Original Message - From: bruce [EMAIL PROTECTED] To: php-general@lists.php.net Sent: Thursday, September 22, 2005 8:05 PM Subject: [PHP] basic user/input form questions... more validation! hi... forgive me!!! Ok; -) Why? You're just asking... :-) continuing the thread from yesterday regarding filtering. (and thanks to all the msgs) for simplicity. let's deal wit a simple user input form, that's going to place the information in a db. if the app allows the user to enter the input (call it 'foo') and then submits the form via a POST, where the data is then written to the db, what kind of validation should occur? and where should the validation take place? What kind of validation depends on your application. If the foo variable must be an integer, then you'll have to check if foo is numeric with is_numberic(). If foo is a string and the length matters, then you would have to validate so the length isn't more than expected with strlen()-function But in all cases you'll have to check if the foo-variable is set with isset. for my $0.02 worth, there should be be validation of the 'foo' var, to determine if the var is legitimate. there should also be validation/filterin of the var when it's placed in the db_sql command... my question (and it's basic), what validation should be performed on the 'foo' var, and why? i've seen htmlspecialchars/magic_quotes/etc.. in varius articles, but i can't find a definitive answer!! You'll have to quote only the variables inside a sql-string. You must use mysql_real_escape_string for creating a safe db-string.. Example: $sql = SELECT ID from Table WHERE Foo= . safeQuote($foo); and the function safeQuote is like this... function safeQuote($value) { // Stripslashes if (get_magic_quotes_gpc()) { $foo = stripslashes($foo); } // Quote if not integer if (!is_numeric($value)) { $value = ' . mysql_real_escape_string($foo) . '; } } I hope this helps a little... /G http://www.varupiraten.se/ also, when inserting/updating a db item, what is the 'correct' process for data? should all data that gets inserted into a db be quoted? if it should, what's the 'standard' practice? psuedo examples of this stuff would be really helpful! thanks for clarifying some of these issues... -bruce [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 2005-09-21 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
bruce mailto:[EMAIL PROTECTED] on Thursday, September 22, 2005 11:05 AM said: if the app allows the user to enter the input (call it 'foo') and then submits the form via a POST, where the data is then written to the db, what kind of validation should occur? Depends on what kind of a form field 'foo' is. Is it a name? A zip code? A phone number? If it's a zip code you can do a simple regex \d{5}(-\d{4})? to make sure it follows the correct (US) format. If it passes the test you know it's safe to be put into the database. This kind of data does not need to be escaped. On the other hand if it's a name you'll first want to make sure it's the correct length and contains only the characters you want it to. If the data passes all the tests you'll definitely want to escape the string before you insert it into the db because some names might have an apostrophe in them which will cause an error during insertion. No need to run htmlspecialchars() in this case since a name that has or (or similar characters) should fail the test anyway. and where should the validation take place? Validation should take place before the value is used. ?php // include files // instantiate any objects if necessary // define default values for page specific variables if necessary // validate incoming data // deal with invalid data by displaying error messages or redirecting // to another page // if data is all clean continue processing like normal ? for my $0.02 worth, there should be be validation of the 'foo' var, to determine if the var is legitimate. there should also be validation/filterin of the var when it's placed in the db_sql command... No need to validate data twice. As stated above, validation should happen before the data is used at all and I would do the escaping just before the data is inserted into the db. my question (and it's basic), what validation should be performed on the 'foo' var, and why? i've seen htmlspecialchars/magic_quotes/etc.. in varius articles, but i can't find a definitive answer!! See above. also, when inserting/updating a db item, what is the 'correct' process for data? should all data that gets inserted into a db be quoted? if it should, what's the 'standard' practice? Again, if the data requires escaping, escape it. If not, there's no need. If the data falls outside the realm of a-zA-Z0-9 it has a high potential for escaping. psuedo examples of this stuff would be really helpful! thanks for clarifying some of these issues... hth, Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
here's psuedo of what i envision now, in my psuedo code, i don't use the stripslashes/quotes/etc... so, i'm still trying to understand why these functions are needed/required? thoughts/comments/etc thanks -bruce -- index.php if isset($REQUEST['submit']) { if (isset($_REQUEST['foo']) { get the 'foo' var $foo = $_REQUEST['foo'] validate the foo var... $valid_chars = preg_match('valid char vars', $foo) $len = str_len($foo) max_str_len if(valid_chars len) { we have a valid 'foo' var!!! go ahead and add to the db... } else { 'foo' is invalid, set err msg and return to login } } else { gen err and redirect to login page } } else { form php_self post input name=foo submit /form } function add_to_db($var) { assume that 'var' is valid, but it hasn't been quoted.. $test_sql = insert into TestTBL name values (%s); $test_sql = sprintf($test_sql, $var); mysql_query($test_sql); } -Original Message- From: Gustav Wiberg [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 11:14 AM To: [EMAIL PROTECTED]; php-general@lists.php.net Subject: Re: [PHP] basic user/input form questions... more validation! - Original Message - From: bruce [EMAIL PROTECTED] To: php-general@lists.php.net Sent: Thursday, September 22, 2005 8:05 PM Subject: [PHP] basic user/input form questions... more validation! hi... forgive me!!! Ok; -) Why? You're just asking... :-) continuing the thread from yesterday regarding filtering. (and thanks to all the msgs) for simplicity. let's deal wit a simple user input form, that's going to place the information in a db. if the app allows the user to enter the input (call it 'foo') and then submits the form via a POST, where the data is then written to the db, what kind of validation should occur? and where should the validation take place? What kind of validation depends on your application. If the foo variable must be an integer, then you'll have to check if foo is numeric with is_numberic(). If foo is a string and the length matters, then you would have to validate so the length isn't more than expected with strlen()-function But in all cases you'll have to check if the foo-variable is set with isset. for my $0.02 worth, there should be be validation of the 'foo' var, to determine if the var is legitimate. there should also be validation/filterin of the var when it's placed in the db_sql command... my question (and it's basic), what validation should be performed on the 'foo' var, and why? i've seen htmlspecialchars/magic_quotes/etc.. in varius articles, but i can't find a definitive answer!! You'll have to quote only the variables inside a sql-string. You must use mysql_real_escape_string for creating a safe db-string.. Example: $sql = SELECT ID from Table WHERE Foo= . safeQuote($foo); and the function safeQuote is like this... function safeQuote($value) { // Stripslashes if (get_magic_quotes_gpc()) { $foo = stripslashes($foo); } // Quote if not integer if (!is_numeric($value)) { $value = ' . mysql_real_escape_string($foo) . '; } } I hope this helps a little... /G http://www.varupiraten.se/ also, when inserting/updating a db item, what is the 'correct' process for data? should all data that gets inserted into a db be quoted? if it should, what's the 'standard' practice? psuedo examples of this stuff would be really helpful! thanks for clarifying some of these issues... -bruce [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 2005-09-21 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
hey chris... so you're sayng that if data is outside of a-zA-Z0-9 ' then it should probably fail the regex anyway.. and it should error out.. if i understnad you, you're also saying that if the information has an ' in it, then it should be escaped, but you didn't say how.! also, what's the function of the 'addslashes', and when is it used?! -bruce -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 11:38 AM To: php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! bruce mailto:[EMAIL PROTECTED] on Thursday, September 22, 2005 11:05 AM said: if the app allows the user to enter the input (call it 'foo') and then submits the form via a POST, where the data is then written to the db, what kind of validation should occur? Depends on what kind of a form field 'foo' is. Is it a name? A zip code? A phone number? If it's a zip code you can do a simple regex \d{5}(-\d{4})? to make sure it follows the correct (US) format. If it passes the test you know it's safe to be put into the database. This kind of data does not need to be escaped. On the other hand if it's a name you'll first want to make sure it's the correct length and contains only the characters you want it to. If the data passes all the tests you'll definitely want to escape the string before you insert it into the db because some names might have an apostrophe in them which will cause an error during insertion. No need to run htmlspecialchars() in this case since a name that has or (or similar characters) should fail the test anyway. and where should the validation take place? Validation should take place before the value is used. ?php // include files // instantiate any objects if necessary // define default values for page specific variables if necessary // validate incoming data // deal with invalid data by displaying error messages or redirecting // to another page // if data is all clean continue processing like normal ? for my $0.02 worth, there should be be validation of the 'foo' var, to determine if the var is legitimate. there should also be validation/filterin of the var when it's placed in the db_sql command... No need to validate data twice. As stated above, validation should happen before the data is used at all and I would do the escaping just before the data is inserted into the db. my question (and it's basic), what validation should be performed on the 'foo' var, and why? i've seen htmlspecialchars/magic_quotes/etc.. in varius articles, but i can't find a definitive answer!! See above. also, when inserting/updating a db item, what is the 'correct' process for data? should all data that gets inserted into a db be quoted? if it should, what's the 'standard' practice? Again, if the data requires escaping, escape it. If not, there's no need. If the data falls outside the realm of a-zA-Z0-9 it has a high potential for escaping. psuedo examples of this stuff would be really helpful! thanks for clarifying some of these issues... hth, Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
bruce mailto:[EMAIL PROTECTED] on Thursday, September 22, 2005 11:58 AM said: hey chris... Hi. so you're sayng that if data is outside of a-zA-Z0-9 ' then it should probably fail the regex anyway.. and it should error out.. (Where did that apostrophe come from? That wasn't in my list on purpose.) Yes and no. It all depends on what kind of data you're expecting. Here are some quick assumptions. (Assuming US style data.) A zip code should only contain: 0-9 - A zip code does not need to be escaped because it doesn't have any special db characters in it like the apostrophe. A name should only contain: a-z A-Z 0-9 - ' A name should be escaped because it might possibly have an apostrophe in it. A phone number should only contain: 0-9 ( ) - . A phone number does not need to be escaped because it doesn't have any special db characters in it like the apostrophe. A paragraph (or rather, very general input) on the other hand is more complicated because it's very application specific. If your paragraph could possibly have some HTML in it and still be valid then of course you would not be using simply a-z A-Z 0-9 for validation. But at the same time you would definitely want to escape the string. if i understnad you, you're also saying that if the information has an ' in it, then it should be escaped, but you didn't say how.! Yes that's correct. Google is your friend. But in any case you can do mysql_escape_string(). (Note: You don't have to deslash your data on the way out, it only happens on the way in.) also, what's the function of the 'addslashes', and when is it used?! I don't use addslahes() much. A good place to start reading would be www.php.net/addslahes hth, Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
further investigation seems to imply that 'strings' that are to be inserted into the mysql db should be 'backslashed' for the chars \x00, \n, \r, \,', and \x1a. this implies that i can have a simple function to accomplish this. the mysql_real_escape_string function requires a db connection and the app might not have opened up a connection to the db at this point in the code.. (or i could rewrite the code!!) the other issue is with 'quoting' items to be inserted in the db. articles that i've seen indicate that the following should be used: numeric data: -doesn't need quoting, but it shouldn't hurt to quote anyway.. (quote all numeric values inserted in the db...) -but wouldn't this require the app to detect numeric vals in the db, and to convert the 'type'!!) -how does this affect date/float vars... string/char data: -backslash all data that's going to be added in the db extracting data from the db: numeric data -get the data/val from the db -check the type/convert the db to int/float/date/etc... string data -get the vals from the db, -strip any slashes that were added to the data/vars -process/use accordingly... have i left anything out..?? thoughts/comments/etc. -bruce -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 11:52 AM To: 'Gustav Wiberg'; php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! here's psuedo of what i envision now, in my psuedo code, i don't use the stripslashes/quotes/etc... so, i'm still trying to understand why these functions are needed/required? thoughts/comments/etc thanks -bruce -- index.php if isset($REQUEST['submit']) { if (isset($_REQUEST['foo']) { get the 'foo' var $foo = $_REQUEST['foo'] validate the foo var... $valid_chars = preg_match('valid char vars', $foo) $len = str_len($foo) max_str_len if(valid_chars len) { we have a valid 'foo' var!!! go ahead and add to the db... } else { 'foo' is invalid, set err msg and return to login } } else { gen err and redirect to login page } } else { form php_self post input name=foo submit /form } function add_to_db($var) { assume that 'var' is valid, but it hasn't been quoted.. $test_sql = insert into TestTBL name values (%s); $test_sql = sprintf($test_sql, $var); mysql_query($test_sql); } -Original Message- From: Gustav Wiberg [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 11:14 AM To: [EMAIL PROTECTED]; php-general@lists.php.net Subject: Re: [PHP] basic user/input form questions... more validation! - Original Message - From: bruce [EMAIL PROTECTED] To: php-general@lists.php.net Sent: Thursday, September 22, 2005 8:05 PM Subject: [PHP] basic user/input form questions... more validation! hi... forgive me!!! Ok; -) Why? You're just asking... :-) continuing the thread from yesterday regarding filtering. (and thanks to all the msgs) for simplicity. let's deal wit a simple user input form, that's going to place the information in a db. if the app allows the user to enter the input (call it 'foo') and then submits the form via a POST, where the data is then written to the db, what kind of validation should occur? and where should the validation take place? What kind of validation depends on your application. If the foo variable must be an integer, then you'll have to check if foo is numeric with is_numberic(). If foo is a string and the length matters, then you would have to validate so the length isn't more than expected with strlen()-function But in all cases you'll have to check if the foo-variable is set with isset. for my $0.02 worth, there should be be validation of the 'foo' var, to determine if the var is legitimate. there should also be validation/filterin of the var when it's placed in the db_sql command... my question (and it's basic), what validation should be performed on the 'foo' var, and why? i've seen htmlspecialchars/magic_quotes/etc.. in varius articles, but i can't find a definitive answer!! You'll have to quote only the variables inside a sql-string. You must use mysql_real_escape_string for creating a safe db-string.. Example: $sql = SELECT ID from Table WHERE Foo= . safeQuote($foo); and the function safeQuote is like this... function safeQuote($value) { // Stripslashes if (get_magic_quotes_gpc()) { $foo = stripslashes($foo); } // Quote if not integer if (!is_numeric($value)) { $value = ' . mysql_real_escape_string($foo) . '; } } I hope this helps a little... /G http://www.varupiraten.se/ also, when inserting/updating a db item, what is the 'correct' process for data? should all data that gets inserted into a db be quoted? if it should, what's the 'standard' practice? psuedo examples of this stuff
Re: [PHP] basic user/input form questions... more validation!
bruce wrote: further investigation seems to imply that 'strings' that are to be inserted into the mysql db should be 'backslashed' for the chars \x00, \n, \r, \,', and \x1a. this implies that i can have a simple function to accomplish this. the mysql_real_escape_string function requires a db connection and the app might not have opened up a connection to the db at this point in the code.. (or i could rewrite the code!!) That would indicate that your code needs rewriting. You should use mysql_real_escape_string as it takes into account the character set in use on your MySQL connection (which is why it needs a DB connection). -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
bruce mailto:[EMAIL PROTECTED] on Thursday, September 22, 2005 3:33 PM said: further investigation seems to imply that 'strings' that are to be inserted into the mysql db should be 'backslashed' for the chars \x00, \n, \r, \,', and \x1a. That's what escaping is. the mysql_real_escape_string function requires a db connection and the app might not have opened up a connection to the db at this point in the code.. (or i could rewrite the code!!) Unless you have warnings print to the screen you should be fine. Or you could just suppress the errors on that one function. numeric data: -doesn't need quoting, but it shouldn't hurt to quote anyway.. (quote all numeric values inserted in the db...) -but wouldn't this require the app to detect numeric vals in the db, and to convert the 'type'!!) No. Why would it? If you quote everything then there's no need to check for type. -how does this affect date/float vars... I'm not sure. Check the MySQL manual on column types. extracting data from the db: numeric data -get the data/val from the db -check the type/convert the db to int/float/date/etc... No type conversion is necessary. PHP is a loose typed language. string data -get the vals from the db, -strip any slashes that were added to the data/vars -process/use accordingly... As I said in my previous email, stripping of slashes is not necessary. The reason data is escaped before it's put into the database is so that you don't confuse the engine. $string_data = Hello I'm a string.; $sql = INSERT INTO table (thestring) VALUES ('$string_data'); That would be the same as: INSERT INTO table (thestring) VALUES 'Hello I'm a string' The engine is going to choke on the apostrophe in I'm. With escaping it would be ... VALUES 'Hello I\'m a string'. When you retrieve that data you'll get exactly Hello I'm a string. There will be no backslash. It also prevents SQL injection attacks. have i left anything out..?? I don't know. hth, Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
hey... regarding .. $string_data = Hello I'm a string.; $sql = INSERT INTO table (thestring) VALUES ('$string_data'); That would be the same as: INSERT INTO table (thestring) VALUES 'Hello I'm a string' The engine is going to choke on the apostrophe in I'm. With escaping it would be ... VALUES 'Hello I\'m a string'. When you retrieve that data you'll get exactly Hello I'm a string. There will be no backslash. It also prevents SQL injection attacks. not sure i agree with this one.. if i put foo \' cat in a db tbl... i expect that i'll get the same out... which is what some of the articles i've seen have stated.. are you telling me, and are you sure, that i'd get foo ' cat out instead!! the articles i've seen imply that if you addslashes, you also need to stripslashes on the backend... comments/thoughts/etc... -bruce -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 3:42 PM To: php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! bruce mailto:[EMAIL PROTECTED] on Thursday, September 22, 2005 3:33 PM said: further investigation seems to imply that 'strings' that are to be inserted into the mysql db should be 'backslashed' for the chars \x00, \n, \r, \,', and \x1a. That's what escaping is. the mysql_real_escape_string function requires a db connection and the app might not have opened up a connection to the db at this point in the code.. (or i could rewrite the code!!) Unless you have warnings print to the screen you should be fine. Or you could just suppress the errors on that one function. numeric data: -doesn't need quoting, but it shouldn't hurt to quote anyway.. (quote all numeric values inserted in the db...) -but wouldn't this require the app to detect numeric vals in the db, and to convert the 'type'!!) No. Why would it? If you quote everything then there's no need to check for type. -how does this affect date/float vars... I'm not sure. Check the MySQL manual on column types. extracting data from the db: numeric data -get the data/val from the db -check the type/convert the db to int/float/date/etc... No type conversion is necessary. PHP is a loose typed language. string data -get the vals from the db, -strip any slashes that were added to the data/vars -process/use accordingly... As I said in my previous email, stripping of slashes is not necessary. The reason data is escaped before it's put into the database is so that you don't confuse the engine. $string_data = Hello I'm a string.; $sql = INSERT INTO table (thestring) VALUES ('$string_data'); That would be the same as: INSERT INTO table (thestring) VALUES 'Hello I'm a string' The engine is going to choke on the apostrophe in I'm. With escaping it would be ... VALUES 'Hello I\'m a string'. When you retrieve that data you'll get exactly Hello I'm a string. There will be no backslash. It also prevents SQL injection attacks. have i left anything out..?? I don't know. hth, Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] basic user/input form questions... more validation!
bruce wrote: not sure i agree with this one.. if i put foo \' cat in a db tbl... i expect that i'll get the same out... which is what some of the articles i've seen have stated.. are you telling me, and are you sure, that i'd get foo ' cat out instead!! the articles i've seen imply that if you addslashes, you also need to stripslashes on the backend... With all due respect, you are demonstrating a fundamental lack of understanding of the principle of escaping anything... The point of escaping the quote mark is so that it looks *exactly like a quote mark* to MySQL, rather than like the delimiter for a string value. MySQL treats the character sequence \' as ' when inside a string value. When you SELECT the data at the other end, MySQL will not give you the slashes, because the slashes are not stored in the database table. If you do run stripslashes() on the output, you will lose data in some situations. So don't. Hope that made sense and helped... -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
one more question/issue... the mysql_real_escape function escapes with a'\' which works for mysql, but isn't standard ansi... is there another function that does the same thing, but uses the ansi standard '. also, if there is another function, does it also work with mysql?? thanks -bruce -Original Message- From: Jasper Bryant-Greene [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 4:39 PM To: php-general@lists.php.net Subject: Re: [PHP] basic user/input form questions... more validation! bruce wrote: not sure i agree with this one.. if i put foo \' cat in a db tbl... i expect that i'll get the same out... which is what some of the articles i've seen have stated.. are you telling me, and are you sure, that i'd get foo ' cat out instead!! the articles i've seen imply that if you addslashes, you also need to stripslashes on the backend... With all due respect, you are demonstrating a fundamental lack of understanding of the principle of escaping anything... The point of escaping the quote mark is so that it looks *exactly like a quote mark* to MySQL, rather than like the delimiter for a string value. MySQL treats the character sequence \' as ' when inside a string value. When you SELECT the data at the other end, MySQL will not give you the slashes, because the slashes are not stored in the database table. If you do run stripslashes() on the output, you will lose data in some situations. So don't. Hope that made sense and helped... -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] basic user/input form questions... more validation!
bruce wrote: the mysql_real_escape function escapes with a'\' which works for mysql, but isn't standard ansi... is there another function that does the same thing, but uses the ansi standard '. also, if there is another function, does it also work with mysql?? Well, you could just use str_replace. But what's the point? A whole lot of MySQL deviates from the standards, often for good reason, and often in ways that makes it easier to use or simply better. There's no functional difference that I know of (correct me if I'm wrong, please), so why bother? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
-Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Friday, 23 September 2005 10:23 AM To: 'Jasper Bryant-Greene'; php-general@lists.php.net Subject: RE: [PHP] basic user/input form questions... more validation! one more question/issue... the mysql_real_escape function escapes with a'\' which works for mysql, but isn't standard ansi... is there another function that does the same thing, but uses the ansi standard '. also, if there is another function, does it also work with mysql?? The important thing here is that escaping with a \ is MySQL's standard for escaping, so should be used when using MySQL as your storage backend. Much warmth, Murray --- Lost in thought... http://www.planetthoughtful.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] basic user/input form questions... more validation!
bruce wrote: not sure i agree with this one.. if i put foo \' cat in a db tbl... i expect that i'll get the same out... which is what some of the articles i've seen have stated.. are you telling me, and are you sure, that i'd get foo ' cat out instead!! the articles i've seen imply that if you addslashes, you also need to stripslashes on the backend... You're reading the wrong articles. :-) Escaping is something you do to preserve data, period. If you have data that is going to enter a context where it can be considered anything other than data, it needs to be escaped. I often simplify this by suggesting that you always escape output. I did a podcast about this topic a few weeks ago: http://pro-php.com/index.php?post_id=10 I also wrote a small followup blog entry: http://shiflett.org/archive/133 Hope that helps. Chris -- Chris Shiflett Brain Bulb, The PHP Consultancy http://brainbulb.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] basic user/input form questions... more validation!
hey chris... thanks!! but what do you mean by ...escape output!! is that output of the query, which is input to the db? as i understand it, you escape the query vars, after filtering/validation, that you're going to touch the db with... (insert/update/select) am i on your same wavelength?? -bruce -Original Message- From: Chris Shiflett [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 7:15 PM To: [EMAIL PROTECTED] Cc: 'Chris W. Parker'; php-general@lists.php.net Subject: Re: [PHP] basic user/input form questions... more validation! bruce wrote: not sure i agree with this one.. if i put foo \' cat in a db tbl... i expect that i'll get the same out... which is what some of the articles i've seen have stated.. are you telling me, and are you sure, that i'd get foo ' cat out instead!! the articles i've seen imply that if you addslashes, you also need to stripslashes on the backend... You're reading the wrong articles. :-) Escaping is something you do to preserve data, period. If you have data that is going to enter a context where it can be considered anything other than data, it needs to be escaped. I often simplify this by suggesting that you always escape output. I did a podcast about this topic a few weeks ago: http://pro-php.com/index.php?post_id=10 I also wrote a small followup blog entry: http://shiflett.org/archive/133 Hope that helps. Chris -- Chris Shiflett Brain Bulb, The PHP Consultancy http://brainbulb.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] basic user/input form questions... more validation!
bruce wrote: but what do you mean by ...escape output!! Output is data that you send somewhere else. In other words, if it leaves your application, it is output. This is explained a bit further (with some code) near the start of this talk: http://brainbulb.com/talks/php-security-audit-howto.pdf Hope that helps. Chris -- Chris Shiflett Brain Bulb, The PHP Consultancy http://brainbulb.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php