• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

encrypted username breaks sql

 
Ranch Hand
Posts: 362
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi guys

i have a slight problem with some of the chars used in encryption.

When a user tries to create an account in my system, the system encrypts the username/password before putting it into the DB.

When the user tries to log in, the system encrypts the username/password used to log in and tries to find a matching encyption in the DB.

problem is, the test username (hsimpson) i just used has a ' in the encyption

this means, the SQL created looks like this:
SELECT username FROM tblUserSecurity WHERE username = '������:Y�`�U'��C�x'

So it' breaking my SQL query and I'm getting an error:
<com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '��C�x'' at line 1>

is there anyway I can stop SQL from using ' in encryption?
 
Author and all-around good cowpoke
Posts: 13078
6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It looks like you are using the binary output of encryption - when interpreted as characters there is a chance you will get an illegal one. To avoid this, base64 encode the binary output. This will always produce legal characters.

Bill
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Use PreparedStatement instead of Statement:
 
Ranch Hand
Posts: 101
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
try this:



That code uses the MD5 encryption in MySQL to encrypt/decrypt the password for you
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Arthur Buliva:
That code uses the MD5 encryption in MySQL to encrypt/decrypt the password for you


MD5 is not a cipher (which is used for encryption/decryption), it's a hash. That means that once something is run through MD5, there's no way to get back the original cleartext. For passwords that is actually the right thing to do, but we should be clear about its one-way nature.
 
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Use PreparedStatement and bind the pasword variable (using setBytes or setString) to the statement object instead of dynamic SQL statement. This will fix this problem

Thanks
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic