File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes encrypted username breaks sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "encrypted username breaks sql" Watch "encrypted username breaks sql" New topic
Author

encrypted username breaks sql

Kevin P Smith
Ranch Hand

Joined: Feb 18, 2005
Posts: 362
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?
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12835
    
    5
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
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19785
    
  20

Use PreparedStatement instead of Statement:


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Arthur Buliva
Ranch Hand

Joined: Mar 08, 2006
Posts: 101
try this:



That code uses the MD5 encryption in MySQL to encrypt/decrypt the password for you
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42928
    
  68
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.
Raees Uzhunnan
Ranch Hand

Joined: Aug 15, 2002
Posts: 126
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


Sun Certified Enterprise Architect
Java Technology Blog
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: encrypted username breaks sql