Query result column separation...

[Stevens]

I know...I stick to char, varchar, and text, but unfortunately I didn't have the pleasure of creating the original db. It's kind of an inheritance. My predecessor had it in Access and updated it via a data source and a hand scanner. I said to heck with that...we're going web! I do need to get an applicatin together in vb or something that will allow me to scan then to sync to our server...but all in the future...thanx again...

E

[Chris]

MySQL is fine with it, trust me.

[chromate]

Yeh, but the fact it's fine with it *is* the problem MySQL is terrible when it comes to data integrity (and referential). It's therefore down to the programmer to treat it with care.

[Stevens]

That's a good thing right? Aren't the programmers supposed to treat it with care anyway? I think the fact that every detail needs attention leaves out a lot of the guess-work...

[chromate]

We're human, we make mistakes. We should be careful, but when it comes to databases their integrity is paramount. It's not so important on smaller databases, but when they get large it's very important and it can be extremely costly to put things right.

Enterprise level DBMS's, such as Oracle for example, will enforce data integrity every-which-way, including datatypes. They in fact reduce the "guess work" by ensuring the database is being used consistently.

[Stevens]

Good point...makes sense tro me. I had tons of issues when coverting the data the first time which was a pain in the butt, but I guess that it's all working the way it should now that all data is consistent. Making the db from scratch sure would have helped. There are just a lot of variables that need to be consistent...so to speak...

E

[Chris]

I like how MySQL lets you quote everything -- it protects you from sql injection attacks.

[chromate]

Injection attacks?

[Chris]

It deals with tainted data.

Someone could figure out how to pass an SQL statement as part of a variable to your script and they could use that statement to muck up your database.

[chromate]

How do quotes guard against that? If anything I would have thought it would make it more vulnerable?

[Chris]

INSERT INTO table set var1 = 897897; delete from main

Where the variable is "897897; delete from main'

Or...

INSERT INTO table set var = '897897; delete from main'

the other way to guard against this is to give your webpages only the type of access they need (read/write/dba).

[chromate]

Interesting point, but if you're inserting numeric data you would need to validate it on a different layer to make sure it's within bounds etc anyway. With text / varchar etc you would use quotes anyway, so there wouldn't be any risk.