*
The moose likes JDBC and the fly likes Trouble with Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trouble with "@" and spaces in mySQL" Watch "Trouble with "@" and spaces in mySQL" New topic
Author

Trouble with "@" and spaces in mySQL

Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
Hello,
I'm using a servlet to write records to a mysql database running on my server. The problem i'm having is that everytime the servlet hits a space or an "@" symbol it kicks me back an SQL exception. Is there a certain data type i should use in mySQL or do i just have to parse all the text boxes and and escape out all the Spaces and @ symbols. Any help is greatly appreciated.
Thanks,
Brian
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
Can you show us some code that demonstrates the problem? Maybe a very simple servlet that inserts some data and generates this exception?


Ron Newman - SCJP 1.2 (100%, 7 August 2002)
Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
sure,
Here is the page i'm working with. give it a try.
http://plpadmin.tempdomainname.com/ServerCard.html
give it a try. I think my problem is maybe my data types in MYsql. I'm using phpMYadmin to administer the database.let me know.
Thanks,
Brian
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
I don't see a link to your servlet or JDBC code there; where is it?
[ July 24, 2003: Message edited by: Ron Newman ]
Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
sorry that is the page that calls the servlet you can test it to see the exception using that but here is the code.

let me know if you need anything else from me. Thanks,
Brian
[ July 24, 2003: Message edited by: Brian K Swingle ]
[ edited to preserve formatting using the [code] and [/code] UBB tags and to break apart long lines -ds ]
[ July 25, 2003: Message edited by: Dirk Schreckmann ]
Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
One more thing just to add to this i went into the phpMYadmin tool and using the SQL window i was able to incert a record with spaces and a "@" symbol with no problem. So somethings weired here i just cant put my finger on it. Thanks for all of your help so far. Let me know what you think.
Thanks
Brian
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
This code is very dangerous. You are not quoting any of the strings that come from the user form, so syntax errors are inevitable.
Even worse things could happen: suppose the user enters something like this into one of the form fields?
foo); drop table ServerCard;
You should use a PreparedStatement to avoid these problems.
Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
I'm relatively inexperienced programmer could you further explain to me what you mean by "quoting" and the benefits there of. and do you have an example of a servlet that uses these methods. I would like to learn the best and safest way to go about inserting user data into a database. Thanks for all your help.
Thanks,
Brian
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
Your generated SQL statement needs to look like

and not


You're going to need to write a static method, something like this:
static String quote(String s) {
return ("'" + s.replace("'","''") + "'");
}
and pass all of the input strings through it before you put them into your SQL statement. There really should be such a static method in the JDBC library; I don't know why there isn't.
A better idea is to use a PreparedStatement, which handles this all for you.
Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
ah i think i get it. So the single tics cause the
information to be treated as a literal and not inturperated in any way. So that most likely whats causing my problem. I'll give that a try.
Thanks,
Brian
Brian K Swingle
Ranch Hand

Joined: Jun 20, 2003
Posts: 39
It worked perfectly thank you for all your help.
Thanks,
Brian Swingle
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trouble with "@" and spaces in mySQL
 
Similar Threads
arrays
JDBC in Applets, problems
rsync blank spaces problem
check style: what to do with tab?
using MySql