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:carl@xxxxxxxx] Sent: Friday, September 23, 2005 9:48 AM To: 'Chris W. Parker'; php-general@xxxxxxxxxxxxx Subject: RE: 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 carl@xxxxxxxx > -----Original Message----- > From: Chris W. Parker [mailto:cparker@xxxxxxxxxxxx] > Sent: Thursday, September 22, 2005 6:42 PM > To: php-general@xxxxxxxxxxxxx > Subject: RE: basic user/input form questions... more validation! > > bruce <mailto:bedouglas@xxxxxxxxxxxxx> > 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
