List:General Discussion« Previous MessageNext Message »
From:JamesDR Date:December 21 2005 4:44pm
Subject:Re: Cleaning illegal characters from varchar field
View as plain text  
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

Thread
Cleaning illegal characters from varchar fieldNathan Gross20 Dec
  • Re: Cleaning illegal characters from varchar fieldSGreen21 Dec
    • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
      • Re: Cleaning illegal characters from varchar fieldSGreen21 Dec
        • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
          • Re: Cleaning illegal characters from varchar fieldSGreen21 Dec
          • Re: Cleaning illegal characters from varchar fieldHassan Schroeder21 Dec
            • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
              • Re: Cleaning illegal characters from varchar fieldHassan Schroeder21 Dec
                • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
                  • Matching phone numbers to addressesRussell Horn21 Dec
                    • Re: Matching phone numbers to addressesPeter Brawley21 Dec
                  • Re: Cleaning illegal characters from varchar fieldHassan Schroeder21 Dec
                    • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
                  • Re: Cleaning illegal characters from varchar fieldMark Matthews21 Dec
                    • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
      • Re: Cleaning illegal characters from varchar fieldJamesDR21 Dec
        • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec