You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Rayed Al-Rashed <ra...@mail.com> on 2000/10/12 02:39:31 UTC

How to escape parameters used in SQL?

Hello everyone,

I am trying to insert a text containg single quotes ( ' ) in a
database, but I get a SQL syntax error!!
Any ideas on how to esacpe single quotes from parameters to be
used in SQL

<sql:execute-query>
  <sql:driver>sun.jdbc.odbc.JdbcOdbcDriver</sql:driver>
  <sql:dburl>jdbc:odbc:MYDB</sql:dburl>
  <sql:query>
	INSERT INTO news(Title, Body)
		VALUES ('<request:get-parameter name="title"/>',
		'<request:get-parameter name="body"/>');
  </sql:query>
</sql:execute-query>


Thanks every one

- Rayed

Re: How to escape parameters used in SQL?

Posted by Donald Ball <ba...@webslingerZ.com>.
On Fri, 13 Oct 2000, Rayed Al-Rashed wrote:

> Unfortunately it didn't work, here is the code, and the stack trace.
> But isn't there a simple way to escape single quotes in simple
> queries, because it is needed for drivers that don't support prepared
> statements any way.

it looks very much like you have no title or body request parameters -
that's why you're getting an npe. try putting just plain text inside the
parametesr instead of calls to request:get-parameter and see.

for drivers that don't support prepared statements, you're on your own for
escaping quotes, there's no standard JBDC way to do it. you could put a
little method in a little library that will escape strings like your
database wants them to be and then call that inside an xsp:expr inside
esql:query:

<esql:query>
 insert into
news(title,body) values('foo','<xsp:expr>com.mine.SQLEscape(<request:get-parameter
name="body"/>)</xsp:expr>')</esql:query>

- donald

> -------------------------------------------------------
> <esql:execute-query>
>   <esql:driver>sun.jdbc.odbc.JdbcOdbcDriver</esql:driver>
>   <esql:dburl>jdbc:odbc:MYDB</esql:dburl>
>   <esql:statement>
>   INSERT INTO news(Title,Body)
>      VALUES (<esql:parameter><request:get-parameter  
> name="title"/></esql:parameter>
>      , <esql:parameter><request:get-parameter
> name="body"/></esql:parameter> );
>   </esql:statement>
> </esql:execute-query>
> -------------------------------------------------------
> java.sql.SQLException: [Microsoft][ODBC Paradox Driver]Invalid use of
> null pointer at
> sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:4089) at
> sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:4246) at
> sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:1172) at
> sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:206) at
> sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:131)
> at
> _C_._Program_Files._Apache_Group._Apache._htdocs._news._article_add._esql_execute_query_Nf67f957c(_article_add.java,
> Compiled Code) at
> _C_._Program_Files._Apache_Group._Apache._htdocs._news._article_add.populateDocument(_article_add.java:383)
> at
> org.apache.cocoon.processor.xsp.XSPPage.getDocument(XSPPage.java:96)
> at
> org.apache.cocoon.processor.xsp.XSPProcessor.process(XSPProcessor.java,
> Compiled Code) at
> org.apache.cocoon.Engine.handle(Engine.java, Compiled Code) at
> org.apache.cocoon.Cocoon.service(Cocoon.java:167) at
> javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at
> org.apache.jserv.JServConnection.processRequest(JServConnection.java:317)
> at
> org.apache.jserv.JServConnection.run(JServConnection.java:188) at
> java.lang.Thread.run(Thread.java:479) 
> -------------------------------------------------------
> 
> Thanks
> 
> - Rayed
> 
> 
> Donald Ball wrote:
> > 
> > On Thu, 12 Oct 2000, Rayed Al-Rashed wrote:
> > 
> > > I installed the latest development snapshot of cocoon, and I
> > > converted my code to esql taglib, but I get this error
> > >   "[ODBC Paradox Driver]Invalid use of null pointer"
> > > any ideas what does this mean, is it something with my code
> > >
> > > <esql:execute-query>
> > >   <esql:driver>sun.jdbc.odbc.JdbcOdbcDriver</esql:driver>
> > >   <esql:dburl>jdbc:odbc:MYDB</esql:dburl>
> > >   <esql:statement>
> > >     INSERT INTO news(Title,Body)
> > >     VALUES ('<request:get-parameter name="title"/>',
> > >     <esql:parameter><request:get-parameter
> > > name="body"/></esql:parameter> );
> > >   </esql:statement>
> > >
> > >   <esql:error-results>
> > >   <message><esql:get-message/></message>
> > >   <stacktrace><esql:get-stacktrace/></stacktrace>
> > >   </esql:error-results>
> > > </esql:execute-query>
> > 
> > i think you need to parameterize both arguments, title and body. i'm not
> > sure though. if that doesn't work, post the full stack trace. note i have
> > no idea if the JDBC-ODBC bridge supports prepared statements or not. i
> > highly recommend you do not use that driver in production environments.
> > 
> > > and does "<esql:statement>" replace "<esql:query>"
> > 
> > yes. although i suppose i could check and see if there is a esql:parameter
> > element underneath or not and switch standard query v.s. prepared
> > statement based on that... any thoughts?
> > 
> > - donald
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> > For additional commands, e-mail: cocoon-users-help@xml.apache.org
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org
> 
> 


Re: How to escape parameters used in SQL?

Posted by Rayed Al-Rashed <ra...@mail.com>.
Unfortunately it didn't work, here is the code, and the stack trace.
But isn't there a simple way to escape single quotes in simple
queries, because it is needed for drivers that don't support prepared
statements any way.

-------------------------------------------------------
<esql:execute-query>
  <esql:driver>sun.jdbc.odbc.JdbcOdbcDriver</esql:driver>
  <esql:dburl>jdbc:odbc:MYDB</esql:dburl>
  <esql:statement>
  INSERT INTO news(Title,Body)
     VALUES (<esql:parameter><request:get-parameter  
name="title"/></esql:parameter>
     , <esql:parameter><request:get-parameter
name="body"/></esql:parameter> );
  </esql:statement>
</esql:execute-query>
-------------------------------------------------------
java.sql.SQLException: [Microsoft][ODBC Paradox Driver]Invalid use of
null pointer at
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:4089) at
sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:4246) at
sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:1172) at
sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:206) at
sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:131)
at
_C_._Program_Files._Apache_Group._Apache._htdocs._news._article_add._esql_execute_query_Nf67f957c(_article_add.java,
Compiled Code) at
_C_._Program_Files._Apache_Group._Apache._htdocs._news._article_add.populateDocument(_article_add.java:383)
at
org.apache.cocoon.processor.xsp.XSPPage.getDocument(XSPPage.java:96)
at
org.apache.cocoon.processor.xsp.XSPProcessor.process(XSPProcessor.java,
Compiled Code) at
org.apache.cocoon.Engine.handle(Engine.java, Compiled Code) at
org.apache.cocoon.Cocoon.service(Cocoon.java:167) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at
org.apache.jserv.JServConnection.processRequest(JServConnection.java:317)
at
org.apache.jserv.JServConnection.run(JServConnection.java:188) at
java.lang.Thread.run(Thread.java:479) 
-------------------------------------------------------

Thanks

- Rayed


Donald Ball wrote:
> 
> On Thu, 12 Oct 2000, Rayed Al-Rashed wrote:
> 
> > I installed the latest development snapshot of cocoon, and I
> > converted my code to esql taglib, but I get this error
> >   "[ODBC Paradox Driver]Invalid use of null pointer"
> > any ideas what does this mean, is it something with my code
> >
> > <esql:execute-query>
> >   <esql:driver>sun.jdbc.odbc.JdbcOdbcDriver</esql:driver>
> >   <esql:dburl>jdbc:odbc:MYDB</esql:dburl>
> >   <esql:statement>
> >     INSERT INTO news(Title,Body)
> >     VALUES ('<request:get-parameter name="title"/>',
> >     <esql:parameter><request:get-parameter
> > name="body"/></esql:parameter> );
> >   </esql:statement>
> >
> >   <esql:error-results>
> >   <message><esql:get-message/></message>
> >   <stacktrace><esql:get-stacktrace/></stacktrace>
> >   </esql:error-results>
> > </esql:execute-query>
> 
> i think you need to parameterize both arguments, title and body. i'm not
> sure though. if that doesn't work, post the full stack trace. note i have
> no idea if the JDBC-ODBC bridge supports prepared statements or not. i
> highly recommend you do not use that driver in production environments.
> 
> > and does "<esql:statement>" replace "<esql:query>"
> 
> yes. although i suppose i could check and see if there is a esql:parameter
> element underneath or not and switch standard query v.s. prepared
> statement based on that... any thoughts?
> 
> - donald
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org

Re: How to escape parameters used in SQL?

Posted by Donald Ball <ba...@webslingerZ.com>.
On Thu, 12 Oct 2000, Rayed Al-Rashed wrote:

> I installed the latest development snapshot of cocoon, and I
> converted my code to esql taglib, but I get this error
>   "[ODBC Paradox Driver]Invalid use of null pointer"
> any ideas what does this mean, is it something with my code
> 
> <esql:execute-query>
>   <esql:driver>sun.jdbc.odbc.JdbcOdbcDriver</esql:driver>
>   <esql:dburl>jdbc:odbc:MYDB</esql:dburl>
>   <esql:statement>
>     INSERT INTO news(Title,Body)
>     VALUES ('<request:get-parameter name="title"/>', 
>     <esql:parameter><request:get-parameter
> name="body"/></esql:parameter> );
>   </esql:statement>
> 
>   <esql:error-results>
>   <message><esql:get-message/></message>
>   <stacktrace><esql:get-stacktrace/></stacktrace>
>   </esql:error-results>
> </esql:execute-query>

i think you need to parameterize both arguments, title and body. i'm not
sure though. if that doesn't work, post the full stack trace. note i have
no idea if the JDBC-ODBC bridge supports prepared statements or not. i
highly recommend you do not use that driver in production environments.

> and does "<esql:statement>" replace "<esql:query>"

yes. although i suppose i could check and see if there is a esql:parameter
element underneath or not and switch standard query v.s. prepared
statement based on that... any thoughts?

- donald


Re: How to escape parameters used in SQL?

Posted by Rayed Al-Rashed <ra...@mail.com>.
I installed the latest development snapshot of cocoon, and I
converted my code to esql taglib, but I get this error
  "[ODBC Paradox Driver]Invalid use of null pointer"
any ideas what does this mean, is it something with my code

<esql:execute-query>
  <esql:driver>sun.jdbc.odbc.JdbcOdbcDriver</esql:driver>
  <esql:dburl>jdbc:odbc:MYDB</esql:dburl>
  <esql:statement>
    INSERT INTO news(Title,Body)
    VALUES ('<request:get-parameter name="title"/>', 
    <esql:parameter><request:get-parameter
name="body"/></esql:parameter> );
  </esql:statement>

  <esql:error-results>
  <message><esql:get-message/></message>
  <stacktrace><esql:get-stacktrace/></stacktrace>
  </esql:error-results>
</esql:execute-query>


and does "<esql:statement>" replace "<esql:query>"
Thanks alot guys

- Rayed



Donald Ball wrote:
> 
> On Wed, 11 Oct 2000, Matthew Cordes wrote:
> 
> > In my database (Oracle) you can escape a single quote ( ' ) with another
> > single quote, thus
> >
> >     SELECT * FROM names where last_name = 'O'' Donnell'
> >
> > should work, but a better solution is prepared statements.  It isn't
> > too hard to rework the sql taglib to add support for a setString( int,
> > String) tag to do the same thing as the PreparedStatement's method of
> > the same name.
> >
> > I think I over heard someone saying prepared statements were on the
> > todo list for the esql, so maybe someday soon that will be another
> > option.
> 
> it's there already in cvs. try it:
> 
> <esql:statement>select * from department_table where name =
> <esql:parameter><request:get-parameter name="name"/></esql:parameter>
> </esql:statement>
> 
> defaults to string but you can make it be anything you want by adding a
> type attribute (e.g. type="int"). i think anyway. i have received _no_
> feedback since initially adding this, so speak up if you want a say in the
> syntax or features.
> 
> - donald
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org

Re: How to escape parameters used in SQL?

Posted by Donald Ball <ba...@webslingerZ.com>.
On Wed, 11 Oct 2000, Matthew Cordes wrote:

> In my database (Oracle) you can escape a single quote ( ' ) with another
> single quote, thus
> 
>     SELECT * FROM names where last_name = 'O'' Donnell'
> 
> should work, but a better solution is prepared statements.  It isn't
> too hard to rework the sql taglib to add support for a setString( int,
> String) tag to do the same thing as the PreparedStatement's method of
> the same name.
> 
> I think I over heard someone saying prepared statements were on the
> todo list for the esql, so maybe someday soon that will be another
> option.

it's there already in cvs. try it:

<esql:statement>select * from department_table where name =
<esql:parameter><request:get-parameter name="name"/></esql:parameter>
</esql:statement>

defaults to string but you can make it be anything you want by adding a
type attribute (e.g. type="int"). i think anyway. i have received _no_
feedback since initially adding this, so speak up if you want a say in the
syntax or features.

- donald


Re: How to escape parameters used in SQL?

Posted by Matthew Cordes <mc...@maine.edu>.
In my database (Oracle) you can escape a single quote ( ' ) with another
single quote, thus

    SELECT * FROM names where last_name = 'O'' Donnell'

should work, but a better solution is prepared statements.  It isn't too
hard to rework
the sql taglib to add support for a setString( int, String) tag to do the
same thing as the
PreparedStatement's method of the same name.

I think I over heard someone saying prepared statements were on the todo
list for the
esql, so maybe someday soon that will be another option.

-matt

----- Original Message -----
From: "Rayed Al-Rashed" <ra...@mail.com>
To: <co...@xml.apache.org>
Sent: Wednesday, October 11, 2000 8:39 PM
Subject: How to escape parameters used in SQL?


> Hello everyone,
>
> I am trying to insert a text containg single quotes ( ' ) in a
> database, but I get a SQL syntax error!!
> Any ideas on how to esacpe single quotes from parameters to be
> used in SQL
>
> <sql:execute-query>
>   <sql:driver>sun.jdbc.odbc.JdbcOdbcDriver</sql:driver>
>   <sql:dburl>jdbc:odbc:MYDB</sql:dburl>
>   <sql:query>
> INSERT INTO news(Title, Body)
> VALUES ('<request:get-parameter name="title"/>',
> '<request:get-parameter name="body"/>');
>   </sql:query>
> </sql:execute-query>
>
>
> Thanks every one
>
> - Rayed
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org
>