apostrophe entered into my form messing up my query

[shevyf]

When users enter an apostrophe and/or double quotes in any of the fields on my
form it messes up the query. The query is reading it as an end quote and the
query then cannot run properly. I hope I am explaining myself clearly. Does
anyone know how to fix this? Thanks

[Julian Roberts]

Use the replace function. eg

Replace(Request("mybox"),"'","''")

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[David Powers]

Julian Roberts wrote:

> Use the replace function. eg
>
> Replace(Request("mybox"),"'","''")

Or, if you're using PHP/MySQL, use addslashes().

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[Michael Fesser]

.oO(shevyf)

>When users enter an apostrophe and/or double quotes in any of the fields on my
>form it messes up the query. The query is reading it as an end quote and the
>query then cannot run properly. I hope I am explaining myself clearly. Does
>anyone know how to fix this? Thanks

User-submitted data _must_ be validated and escaped if necessary before
using it in a query. How to do it depends on your DB and scripting
language.

Your script is vulnerable to SQL injection (google for more informations
about that).

Micha

[Michael Fesser]

.oO(David Powers)

>Julian Roberts wrote:

>> Use the replace function. eg
>>
>> Replace(Request("mybox"),"'","''")

>
>Or, if you're using PHP/MySQL, use addslashes().

mysql_real_escape_string() is the proper way.

Micha

[shevyf]

I appreciate your response. I am using ASP and JavaScript. It is not security sensative information just general user information. When validating I can remove the apostrophe using a function? Thanks

[shevyf]

Thanks- I got it working with the ' but not with the '
because when I do Replace(title, ''',' ' ) it treats the middle ' as an end quote. Is there a way around this?

[bthouin]

Hi Jules,

i just added that famous replace of yours because I have some user input
which could contain it. But It leads me to another big problem. Let me
explain (I am using VBScript, ASP, and SQLServer):
- the app I am developing is handling data which is currently held in
Excel spreadsheets. I have about 10 columns, and typically only about 20
rows. To maintain the data, my app is showing the "spreadsheet" data in
a table. The data is read from a SQLServer DB.
- underneath the "sheet", I have a data maintenance area, a table with
10 columns with 10 text fields for the 10 columns of the "sheet". The
user can enter new data or modify existing "cells" of the sheet through
these text fields
- the code reading these out (Request.Form()) DOESN'T know of which type
they are. They can be string, and they can be numbers. This is defined
in a database, from which I generate the text fields entirely
automatically. The fields are read into an array like this:
inp(0) = Request.Form("A")
inp(1) = Request.Form("B")
inp(2) = Request.Form("C")
where A is the content of "column" A of the "sheet"
- On some of the fields (which are numbers), Excel-like operations are
performed, such as column C = col A + col B. This I have programmed and
it all works fine
- now, to be sure about the string fields, I added your replace function
like this
inp(0) = Replace(Request.Form("A"), "'", "''")
inp(1) = Replace(Request.Form("B"), "'", "''")
inp(2) = Replace(Request.Form("C"), "'", "''")
- this promptly resulted in the infamous "Type mismatch" on this line of
code, which is adding 2 "cells" in "columns" A and B: inp(2) =
CStr(CLng(inp(0)) + CLng(inp(1)))

Why ??? I thought CLng was able to cope with any data type ? Why is the
"Replace" changing the data type so that using CLng results in a Type
mismatch ???

How shall I do it?

Regards
Bernard

Julian Roberts wrote:

> Use the replace function. eg
>
> Replace(Request("mybox"),"'","''")
>

[David Powers]

Michael Fesser wrote:

> mysql_real_escape_string() is the proper way.

Without wishing to be confrontational, why is it the "proper way"? I
have checked the PHP online documentation and about half a dozen books,
including those written by Andi Gutmans and Rasmus Lerdorf (who, as lead
developers of PHP, might be expected to recommend best practices), but
nothing seems to suggest either why mysql_real_escape_string() may be
preferable or what may be wrong in using addslashes().

If you can point me to a decent explanation, I'll be very grateful.

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[Michael Fesser]

.oO(David Powers)

>Michael Fesser wrote:

>> mysql_real_escape_string() is the proper way.

>
>Without wishing to be confrontational, why is it the "proper way"?

addslashes() is more or less a generic function, escaping just the most
important chars. In many cases that's not enough for database security.

Every DB uses its own way of escaping special chars, on some servers for
example you have to double a char to escape it. Additionally there could
be some other chars that might cause problems if used unescaped in a
query. That's why DB-specific escaping functions exists and why you
should use them (if available) instead of addslashes().

From <http://www.php.net/manual/en/security.database.sql-injection.php>:

"Quote each non numeric user supplied value that is passed to the
database with the database-specific string escape function [...]"

From
<http://www.php.net/manual/en/function.mysql-real-escape-string.php>:

"mysql_real_escape_string() calls MySQL's library function
mysql_escape_string, which prepends backslashes to the following
characters: NULL, \x00, \n, \r, \, ', " and \x1a."

Micha

[David Powers]

Michael Fesser wrote:

> addslashes() is more or less a generic function, escaping just the most
> important chars. In many cases that's not enough for database security.

Thanks for that explanation. The useful line in one of the pages you
referred to was "If a database-specific string escape mechanism is not
available, the addslashes() and str_replace() functions may be
useful..." In other words, addslashes() is not wrong, but using a
function specific to a particular database is better.

Interestingly, "PHP 5 Power Programming" by Andi Gutmans and others
makes no mention of this, but simply advises to use addslashes(). The
more I read the book, the less impressed I am. It seems to have been
thrown together in a hurry.

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[Julian Roberts]

Not quite sure there Bernard. To check what you're inputting use

Response.Write strSQL : Response.End

This will write out the SQL to the browser and you may spot your error.

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[bthouin]

Julian,

No SQL involved at all !

OK, let me rephrase my problem, I obviously havent't been clear enough:
A) working setup:
a = Request.Form("MyTextField1")
b = Request.Form("MyTextField2")
.....

c = CStr(CLng(a) + CLng(b))

This properly adds up the 2 numbers which have been actually input in
the 2 text fields. If the 1st field was holding "10" and the 2nd one
"20", then I get a value in c of "30". Perfect !

B) "Enhancement": because some of my fields hold real texts, and the
user COULD enter a quote in them, I add Replace() like this, blindly, on
ALL text fields:
a = Replace(Request.Form("MyTextField1"), "'", "''")
b = Replace(Request.Form("MyTextField2"), "'", "''")
.....

c = CStr(CLng(a) + CLng(b))

This CRASHES, with a "Type mismatch: CLng" on the last code line (the
addition).

WHY ?????

Bernard

Julian Roberts wrote:

> Not quite sure there Bernard. To check what you're inputting use
>
> Response.Write strSQL : Response.End
>
> This will write out the SQL to the browser and you may spot your error.
>