Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

chars \ or / removed from String when added to database into field type VARCHAR?

 
Annemarie McKeown
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a string which is basically the url to a particaluar file - eg
C:\jakarta-tomcat-3.3.1a\jakarta-tomcat-3.3.1a\webapps\test\uploads\general_info.txt
When I want to add this string into my mySQL database field type VARCHAR,
the characters \ or / are removed and the string contained in the database becomes:
C:jakarta-tomcat-3.3.1ajakarta-tomcat-3.3.1awebappstestuploadsgeneral_info.txt

Why is this? I need to keep the \ chars in because I will use it for reference for user to download in the future?
What do I need to do to keep them in?
Probably something simple like changing the field type?
Some help please.
 
David Peterson
author
Ranch Hand
Posts: 154
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you post the code that adds the string to the database? It's hard to know what the problem is without seeing the code.
 
Annemarie McKeown
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The following gets the String from the previous page:
String location = request.getParameter("location");
ie content is: C:\jakarta-tomcat-3.3.1a\JSP\uploads\file.txt
And prints out on the process page the correct string as inputted.
The following adds the String to the database into field location of type VARCHAR(200):
....
stmt.executeUpdate("insert into fileuploads (location) values('"+location+"')");
When it gets added to the database the entry is C:jakarta-tomcat-3.3.1aJSPuploadsfile.txt
Is it something to do with \ is an escape character in Java?
Do you know how to help?
Annemarie
 
Christopher Zinn
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, the \ is getting escaped (more likely by your JDBC driver / SQL Server implementation). To get around this, use a PreparedStatement instead of a Statement. Your code would look something like this:

The PreparedStatement method is also much safer; it prevents people from
doing nasty things like entering "';drop table fileuploads" as there filename.
- Chris
 
Annemarie McKeown
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have implemented your code but I get the following error -
java.sql.SQLException: Syntax error or access violation, message from server: "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 'insert into fileuploads (location) values('C:\\jakarta-tomcat-3"
seems like it cuts off the rest of the String...doesn't get the ); at the end of the insert statement??
My code is as yours was...
PreparedStatement ps = myConn.prepareStatement("(insert into fileuploads (location) values(?)");
ps.setString(1, location);
ps.executeUpdate();
Have I implemented this wrong? Its getting part of the String location, but not all of it.
the next char in that string would be a "." would it be anything to do with that - I wouldn't think so.
Can you help me fix this?
[ March 01, 2004: Message edited by: Annemarie McKeown ]
[ March 01, 2004: Message edited by: Annemarie McKeown ]
 
Christopher Zinn
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are correct, the code should read:

The ( before the insert was a mistake when I typed it in.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic