|
|
Posted by "Carl Furst" on 10/21/67 11:27
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@vote.com
> -----Original Message-----
> From: Chris W. Parker [mailto:cparker@swatgear.com]
> 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:bedouglas@earthlink.net>
> 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.
Navigation:
[Reply to this message]
|