Thread: Special characters in SQL queries

Special characters in SQL queries

From
Vernon Wu
Date:
First a single quote in text, when a backforward slash in a file path, what other special characters need padded in two

backslashes before using the data in a SQL statement?

After hours' search in the PostgreSQL archive, I find a releted information on the http://www.ca.postgresql.org/users-
lounge/docs/7.2/postgres/arrays.html.  It's about array data though.

How can I find out those characters so that I can pre-process them before a SQL query?

Thanks for your information.

Vernon 







Re: Special characters in SQL queries

From
Christoph Haller
Date:
>
> First a single quote in text, when a backforward slash in a file path,
what other special characters need padded in t
wo
> backslashes before using the data in a SQL statement?
>
> After hours' search in the PostgreSQL archive, I find a releted
information on the http://www.ca.postgresql.org/users
-
> lounge/docs/7.2/postgres/arrays.html.  It's about array data though.
>
> How can I find out those characters so that I can pre-process them
before a SQL query?
>
What about
size_t PQescapeString (char *to, const char *from, size_t length);
from the  libpq - C Library?

Regards, Christoph




Re: Special characters in SQL queries

From
Vernon Wu
Date:
Thank Christoph for your information.

I don't program in C at all, but Java. Although I can make a way to call the C library function from Java, it isn't
suitable
 
solution, I believe.

I only desire to find out all the type of characters at the present time so that I can strip out any potential problem
sources
 
in text. Who know what characters a user will enter.

Vernon  

11/03/2003 7:32:33 AM, Christoph Haller <ch@rodos.fzk.de> wrote:

>>
>> First a single quote in text, when a backforward slash in a file path,
>what other special characters need padded in t
>wo
>> backslashes before using the data in a SQL statement?
>>
>> After hours' search in the PostgreSQL archive, I find a releted
>information on the http://www.ca.postgresql.org/users
>-
>> lounge/docs/7.2/postgres/arrays.html.  It's about array data though.
>>
>> How can I find out those characters so that I can pre-process them
>before a SQL query?
>>
>What about
>size_t PQescapeString (char *to, const char *from, size_t length);
>from the  libpq - C Library?
>
>Regards, Christoph
>
>
>





Re: Special characters in SQL queries

From
"Victor Yegorov"
Date:
* Vernon Wu <vernonw@gatewaytech.com> [12.03.2003 05:36]:
> Thank Christoph for your information.
>
> I don't program in C at all, but Java. Although I can make a way to call the C library function from Java, it isn't
suitable 
> solution, I believe.
>
> I only desire to find out all the type of characters at the present time so that I can strip out any potential
problemsources  
> in text. Who know what characters a user will enter.
>

Take a look at the source code of PQescapeString() in the source
distribution of postgres. I hope, you'll find all the chars you're
interested in.

> >>
> >> First a single quote in text, when a backforward slash in a file path,
> >what other special characters need padded in t
> >wo
> >> backslashes before using the data in a SQL statement?
> >>
> >> After hours' search in the PostgreSQL archive, I find a releted
> >information on the http://www.ca.postgresql.org/users
> >-
> >> lounge/docs/7.2/postgres/arrays.html.  It's about array data though.
> >>
> >> How can I find out those characters so that I can pre-process them
> >before a SQL query?
> >>
> >What about
> >size_t PQescapeString (char *to, const char *from, size_t length);



--

Victor Yegorov

Re: Special characters in SQL queries

From
Christoph Haller
Date:
>
> I don't program in C at all, but Java. Although I can make a way to
call the C library function
> from Java, it isn't suitable solution, I believe.
> I only desire to find out all the type of characters at the present
time so that I can strip out any
> potential problem sources in text. Who know what characters a user
will enter.
>
As Victor Yegorov mentioned
Take a look at the source code of PQescapeString() in the source
distribution of postgres. I hope, you'll find all the chars you're
interested in.
or refer to Binary Strings within the Data Types chapter of the doc.
This shows how to deal with "non-printables" and "printables".

Regards, Christoph




Re: Special characters in SQL queries

From
Richard Huxton
Date:
On Wednesday 12 Mar 2003 3:28 am, Vernon Wu wrote:
> Thank Christoph for your information.
>
> I don't program in C at all, but Java. Although I can make a way to call
> the C library function from Java, it isn't suitable solution, I believe.
>
> I only desire to find out all the type of characters at the present time so
> that I can strip out any potential problem sources in text. Who know what
> characters a user will enter.

Don't do Java myself, but I can't believe the JDBC (or whatever) classes don't
do this for you. In any case, you can look at the code for the C function -
it should be fairly obvious what characters it's working on.

If you do want to write your own, it's best not to strip certain characters,
but rather to list those you will allow through. That way if you miss
something it's a bug report rather than a security hole.

--  Richard Huxton


Re: Special characters in SQL queries

From
Rupa Schomaker
Date:
-----BEGIN PGP SIGNED MESSAGE-----

Vernon Wu <vernonw@gatewaytech.com> writes:

> I don't program in C at all, but Java. Although I can make a way to
> call the C library function from Java, it isn't suitable solution, I
> believe.
>
> I only desire to find out all the type of characters at the present
> time so that I can strip out any potential problem sources in
> text. Who know what characters a user will enter.

Why not strictly use bind variables?  That way you never have to worry
about escaping and you get the added bonus of using prepared
statements?

- -- 
- -rupa

-----BEGIN PGP SIGNATURE-----
Version: 2.6.3ia
Charset: noconv
Comment: Processed by Mailcrypt 3.5.7, an Emacs/PGP interface

iQEVAwUBPm7KqnHDM4ucEopdAQFs+Af/d2DMjOWrLhN5S0TdwJGrelvYMwCvpSd3
QZrdx2fv3tGHx1JjqlZgKRKrn08GWMe+AjN2I89FfjyG0vurOqLSAJA4pGI0guGF
9cqXWQJTXd5yMEencG3Va7VhF8WcEmGX1iC891s7xdssmQW1myKZIKwrLPO3jVla
SRFqh4xPycyJk8L6McbWoGwQa5I+xvLKWlS38tr1tQZu4bbg0o4pDnTYVLvdswH/
oM6ZOk4fHwK7/E4071jNYTWe9azmylJfWpqJCELc+9k4afibcdOTLXvWk0Re438d
6wGadJ5tpT65onaSYzpUENTmedY1CPAG+ViL+t8rMgBI/jGoKFXxJg==
=t25X
-----END PGP SIGNATURE-----