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
>