Nathan Gross wrote:
<snip>
I get two lists from different sources which I merge into the database
via a Java program. Since these two lists themselves sometimes get
their data from the same source, my program first does a SELECT on the
varchar field (unique index) [to ensure that this data is not yet in
the db] before inserting the data. (Actually I am using j2ee
ejbFinds.)
Occasionaly, say 5% of the time, I get a duplicate (create) exception
from the db, even though the db just told my program that the data was
not there!
With my debugger I verified that for these fields, if you try to
find/select this data, mysql will return a null resultset, but yet if
you try to enter this data it will throw the create exception.
Now these [defective] fields always have (1 or more) suspicious
characters like {,[,',", or commas colons, what not. I do not know
which of these is/are the culprits.
So, my little problem is twofold.
1) How to trap this data in the first place in my Java program.
Obviously I need to know what to look for!
2) I do not want to leave my database in this state, so I want to clean it.
2b)I don't mind adding a boolean field to the table to be used as a
flag for all records that have suspect data, before deciding exactly
how to clean it. Although I think I can just replace these characters
with a space, in [almost] all cases.
One more important point. This field, although unique, is NOT the pk.
I can use the PK to correctly select and display the contents of all
fields in the record.
Thank you much.
-nat
Sounds like, to me, that you are not properly sanitizing the information
before doing your select. This is very dangerous and you leave yourself
open to SQL injection attacks. You may want to look at the same time as
you are trying to 'clean' your DB of these special characters. I have
many DB's here with special (illegal) characters, properly sanitized
before inserting, and in my select statements.
If you want more info about SQL injection attacks (elementary must know
for anyone making a DB based app) have a Google on the topic. There are
many ways to avoid it.
Sorry it's slightly OT. But it seems you aren't sanitizing the data in
before using it. Trust no data from any source (even the DB itself) to
be 'clean' :-D
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
