• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Handling single quotes for an insert

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic