Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
Hello, I used some method of RAEscapingSqlPrinter to escape the quotes. And it is working nicely now. I added the escaping method in Garage: String >> asPostgresEscapedSql You have to call it on the "unsafe strings" before executing the query (prepared statement or not). Thanks Pierce! Cheers, Vincent > -Message d'origine- > De : Pharo-users [mailto:pharo-users-boun...@lists.pharo.org] De la part de > Pierce Ng > Envoyé : vendredi 12 août 2016 17:44 > À : Any question about pharo is welcome > Objet : Re: [Pharo-users] [Garage] How to espace sql special chars in a query? > > On Fri, Aug 12, 2016 at 01:36:21PM +0200, Blondeau Vincent wrote: > > > Which database? > > Postgres > > PostgresV2 driver does not support the PostgreSQL extended query protocol > which allows prepared statements, AFAIK. > > https://www.postgresql.org/docs/current/static/protocol-overview.html. > > As for escaping SQL, the ROE package has a class called RAEscapingSqlPrinter > which may help. > > http://smalltalkhub.com/#!/~PharoExtras/ROE > > Pierce !!!* "Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis. This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.!!!"
Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
On Fri, Aug 12, 2016 at 01:36:21PM +0200, Blondeau Vincent wrote: > > Which database? > Postgres PostgresV2 driver does not support the PostgreSQL extended query protocol which allows prepared statements, AFAIK. https://www.postgresql.org/docs/current/static/protocol-overview.html. As for escaping SQL, the ROE package has a class called RAEscapingSqlPrinter which may help. http://smalltalkhub.com/#!/~PharoExtras/ROE Pierce
Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
Hi Vincent, I don't know which special characters are you trying to escape, but if simply send printString to the variable, it will escape any quote that would end the string literal. | variable statement | variable := 'Pharo''s O''Reilly book'. statement := 'SELECT ', variable printString. Transcript show: statement; cr. I don't know how Garage prepared statements work, because it doesn't follow the "?" placeholder convention. Regards, Esteban A. Maringolo 2016-08-12 5:34 GMT-03:00 Blondeau Vincent : > Hello, > > > > I am looking for a method that escape special characters for SQL queries. > > E.g.: I want to escape : ‘ in a where expression: ‘….Where field1 = ‘’‘, > myvariable , ‘‘’ …..’ with myvariable := ‘don’’t do’. > > I am using garage and haven't found it in the package. Does someone know > where I can found it? > > > > Thanks in advance, > > > > Best regards, > > Vincent > > > > > !!!* > "Ce message et les pièces jointes sont confidentiels et réservés à l'usage > exclusif de ses destinataires. Il peut également être protégé par le secret > professionnel. Si vous recevez ce message par erreur, merci d'en avertir > immédiatement l'expéditeur et de le détruire. L'intégrité du message ne > pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra > être recherchée quant au contenu de ce message. Bien que les meilleurs > efforts soient faits pour maintenir cette transmission exempte de tout > virus, l'expéditeur ne donne aucune garantie à cet égard et sa > responsabilité ne saurait être recherchée pour tout dommage résultant d'un > virus transmis. > > This e-mail and the documents attached are confidential and intended solely > for the addressee; it may also be privileged. If you receive this e-mail in > error, please notify the sender immediately and destroy it. As its integrity > cannot be secured on the Internet, the Worldline liability cannot be > triggered for the message content. Although the sender endeavours to > maintain a computer virus-free network, the sender does not warrant that > this transmission is virus-free and will not be liable for any damages > resulting from any virus transmitted.!!!"
Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
> On 12 Aug 2016, at 13:36, Blondeau Vincent
> wrote:
>
>
> I think that ' close the EXECUTE query and is not escaped by garage.
you are right. The statement is very sub-optimal (but should be easy to fix).
"If not it means by the moment that we are a named prepared statement
and we execute that"
argumentsString := arguments
ifEmpty: [ '' ]
ifNotEmpty: [ '(''', (''', ''' join: (arguments collect:
#asString)), ''')' ].
^ 'EXECUTE ', (self propertyAt: #statementId), argumentsString
In GNU Smalltalk[1] I had used FFI to use libpg/PQexecParams[2] that allows to
pass query and parameters separately. Garage implements the wire protocol but
it should be possible to pass the parameters separately as well. It should be
simple to use/add this protocol.
holger
[1]
https://github.com/zecke/gnu-smalltalk-debian/blob/master/packages/dbd-postgresql/Connection.st#L185
[2] https://www.postgresql.org/docs/9.1/static/libpq-exec.html
Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
> -Message d'origine-
> De : Pharo-users [mailto:pharo-users-boun...@lists.pharo.org] De la part de
> Holger Freyther
> Envoyé : vendredi 12 août 2016 13:22
> À : Any question about pharo is welcome
> Objet : Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
>
>
> > On 12 Aug 2016, at 12:10, Blondeau Vincent
> wrote:
> >
> > BTW, even with prepared statements, it doesn't work either:
> > SQL query : EXECUTE preparedStmtd2qbaa1ap7ceiaq643sxlkyyw('Quand
> l'utilisateur est connecté sur "son serveur"', '1277')
> > -> 'ERREUR: erreur de syntaxe sur ou près de « utilisateur » au caractère
> > 56'
> (Syntax error near char 56)
>
> Which database?
Postgres
> What does the statement look like?
SELECT id FROM method WHERE name = $1 AND class_id = $2
> What are the bound variables?
>
> E.g. is the string supposed to be UTF8 or a blob?
CREATE TABLE method
(
Id serial,
name character varying,
class_id integer
)
The database scheme is configured in UTF8 -> en_US.UTF8
I think that ' close the EXECUTE query and is not escaped by garage.
Vincent
!!!*
"Ce message et les pièces jointes sont confidentiels et réservés à l'usage
exclusif de ses destinataires. Il peut également être protégé par le secret
professionnel. Si vous recevez ce message par erreur, merci d'en avertir
immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant
être assurée sur Internet, la responsabilité de Worldline ne pourra être
recherchée quant au contenu de ce message. Bien que les meilleurs efforts
soient faits pour maintenir cette transmission exempte de tout virus,
l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne
saurait être recherchée pour tout dommage résultant d'un virus transmis.
This e-mail and the documents attached are confidential and intended solely for
the addressee; it may also be privileged. If you receive this e-mail in error,
please notify the sender immediately and destroy it. As its integrity cannot be
secured on the Internet, the Worldline liability cannot be triggered for the
message content. Although the sender endeavours to maintain a computer
virus-free network, the sender does not warrant that this transmission is
virus-free and will not be liable for any damages resulting from any virus
transmitted.!!!"
Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
> On 12 Aug 2016, at 12:10, Blondeau Vincent
> wrote:
>
> BTW, even with prepared statements, it doesn't work either:
> SQL query : EXECUTE preparedStmtd2qbaa1ap7ceiaq643sxlkyyw('Quand
> l'utilisateur est connecté sur "son serveur"', '1277')
> -> 'ERREUR: erreur de syntaxe sur ou près de « utilisateur » au caractère
> 56' (Syntax error near char 56)
Which database?
What does the statement look like?
What are the bound variables?
E.g. is the string supposed to be UTF8 or a blob?
Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
BTW, even with prepared statements, it doesn't work either:
SQL query : EXECUTE preparedStmtd2qbaa1ap7ceiaq643sxlkyyw('Quand l'utilisateur
est connecté sur "son serveur"', '1277')
-> 'ERREUR: erreur de syntaxe sur ou près de « utilisateur » au caractère 56'
(Syntax error near char 56)
Vincent
> -Message d'origine-
> De : Pharo-users [mailto:pharo-users-boun...@lists.pharo.org] De la part de
> Holger Freyther
> Envoyé : vendredi 12 août 2016 10:45
> À : Any question about pharo is welcome
> Objet : Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
>
>
> > On 12 Aug 2016, at 10:34, Blondeau Vincent
> wrote:
> >
> > Hello,
>
> Hi
>
>
> > I am looking for a method that escape special characters for SQL queries.
> > E.g.: I want to escape : ‘ in a where expression: ‘….Where field1 = ‘’‘,
> myvariable , ‘‘’ …..’ with myvariable := ‘don’’t do’.
> > I am using garage and haven't found it in the package. Does someone know
> where I can found it?
>
>
> I have used prepared statements for that. They allow me to bind the variable
> to the statement and I don't have to worry about escaping (at least that is
> the
> theory).
>
> holger
!!!*
"Ce message et les pièces jointes sont confidentiels et réservés à l'usage
exclusif de ses destinataires. Il peut également être protégé par le secret
professionnel. Si vous recevez ce message par erreur, merci d'en avertir
immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant
être assurée sur Internet, la responsabilité de Worldline ne pourra être
recherchée quant au contenu de ce message. Bien que les meilleurs efforts
soient faits pour maintenir cette transmission exempte de tout virus,
l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne
saurait être recherchée pour tout dommage résultant d'un virus transmis.
This e-mail and the documents attached are confidential and intended solely for
the addressee; it may also be privileged. If you receive this e-mail in error,
please notify the sender immediately and destroy it. As its integrity cannot be
secured on the Internet, the Worldline liability cannot be triggered for the
message content. Although the sender endeavours to maintain a computer
virus-free network, the sender does not warrant that this transmission is
virus-free and will not be liable for any damages resulting from any virus
transmitted.!!!"
Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
Hi, Prepared statements can be a solution but I would like to have a simpler one. In the worst case, I may use that. Vincent > -Message d'origine- > De : Pharo-users [mailto:pharo-users-boun...@lists.pharo.org] De la part de > Holger Freyther > Envoyé : vendredi 12 août 2016 10:45 > À : Any question about pharo is welcome > Objet : Re: [Pharo-users] [Garage] How to espace sql special chars in a query? > > > > On 12 Aug 2016, at 10:34, Blondeau Vincent > wrote: > > > > Hello, > > Hi > > > > I am looking for a method that escape special characters for SQL queries. > > E.g.: I want to escape : ‘ in a where expression: ‘….Where field1 = ‘’‘, > myvariable , ‘‘’ …..’ with myvariable := ‘don’’t do’. > > I am using garage and haven't found it in the package. Does someone know > where I can found it? > > > I have used prepared statements for that. They allow me to bind the variable > to the statement and I don't have to worry about escaping (at least that is > the > theory). > > holger !!!* "Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis. This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.!!!"
Re: [Pharo-users] [Garage] How to espace sql special chars in a query?
> On 12 Aug 2016, at 10:34, Blondeau Vincent > wrote: > > Hello, Hi > I am looking for a method that escape special characters for SQL queries. > E.g.: I want to escape : ‘ in a where expression: ‘….Where field1 = ‘’‘, > myvariable , ‘‘’ …..’ with myvariable := ‘don’’t do’. > I am using garage and haven't found it in the package. Does someone know > where I can found it? I have used prepared statements for that. They allow me to bind the variable to the statement and I don't have to worry about escaping (at least that is the theory). holger
