• Post Reply Bookmark Topic Watch Topic
  • New Topic

Handling single quotes for an insert

 
B Bruford
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm doing a simple insert into an Oracle table, but it blows up if any of the fields contain a single quote. Does JSP have an equivilant to ASP's Replace function?
Here's a simplified version of the code (there are actually many more fields):
<%
String employee = null;
try {
%>
<req:existsParameter name="employee">
<%employee = request.getParameter("employee");
%>
</req:existsParameter>
<sql:connection id="conn1">
<sql:url>jdbcracle:thin:air/air@172.20.96.10:1521:sunfire</sql:url>
<sql:driver>oracle.jdbc.driver.OracleDriver</sql:driver>
</sql:connection>
<sql:preparedStatement id="stmt1" conn="conn1">
<%
sql = "insert into AIR_ACCIDENTS (EMPLOYEE_NAME) values ('" + employee + "')"
%>
<sql:query>
<%=sql%>
</sql:query>
<sql:execute>

</sql:execute>
</sql:preparedStatement>
How can I handle an entry from John O'Malley?
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yuck, SQL in a JSP
I'm not sure where these sql tags come from, but if they support PreparedStatements instead of Statements, use them instead. A PreparedStatement will do the escaping automatically for you.
Dave.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35709
408
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I agree with David. If you absolutely can't use prepared statements, you can use the String class method replace() in java 1.3 or replaceAll() in java 1.4.
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65518
105
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One point of the previous responses is that, in general, it is considered less-than-stellar practice to perform SQL queries directly from the UI layer -- and particularly directly from JSP pages. Are these the JSTL tags you are using? Even many proponents of the JSTL will say that the SQL set of tags are meant for quick proto-typing (I won't even use them for that -- which was the subject of a previous topic in which I was labeled a "Pattern Nazi") and were never meant to be used in "real" code.
While there are a whole slew of extremely valid architectural reason why this is a poor practice, one practical reason is the very topic of your post.
Were you to be using a PreparedStatement in Java code (perferably neatly ensconsed somewhere behind a nice abstraction of your model), you would have much more control over exactly what is going on. Trust me in that continuing down the SQL tag path will only add to the flat spot on the side of your head.
bear
[ January 14, 2004: Message edited by: Bear Bibeault ]
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!