Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Trouble with "@" and spaces in mySQL

 
Brian K Swingle
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show us some code that demonstrates the problem? Maybe a very simple servlet that inserts some data and generates this exception?
 
Brian K Swingle
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It worked perfectly thank you for all your help.
Thanks,
Brian Swingle
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic