List:General Discussion« Previous MessageNext Message »
From:Nathan Gross Date:December 21 2005 5:35pm
Subject:Re: Cleaning illegal characters from varchar field
View as plain text  
On 12/21/05, JamesDR <james@stripped> wrote:
> 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
>
I will have to research this topic. Thanks for the heads up.
-nat
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