how to prevent inputting quotation mark (')?

[Pat]

Hi,

I made a form where visitors can introduce data.
I use this:
strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
dat & ...)"

My problem is that when someone introduces a quotation mark, i get a error
and the insert fails (e.g. nam'e).

How can i prevent that? Controling each entered character seems me to be a
very big work ...

[Martijn Tonies]

> I made a form where visitors can introduce data.
> I use this:
> strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
> dat & ...)"
>
> My problem is that when someone introduces a quotation mark, i get a error
> and the insert fails (e.g. nam'e).
>
> How can i prevent that?

Use prepared statements.

>Controling each entered character seems me to be a
> very big work ...

I believe MySQL has a function for that in the API ...
mysql_escape_string or something.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
[url]http://www.upscene.com[/url]
Database development questions? Check the forum!
[url]http://www.databasedevelopmentforum.com[/url]

[Mike]

Can't you use the replace function (replacing the quotation mark with
an empty string).

strsql = "INSERT INTO mytable (field1) values (replace(" & lol & ",
char(39), ''))"

[Martijn Tonies]

> Can't you use the replace function (replacing the quotation mark with

> an empty string).
>
> strsql = "INSERT INTO mytable (field1) values (replace(" & lol & ",
> char(39), ''))"

Well, although that might solve the quote problem, it does change
the data being saved.

For example, if this was an application that keeps user accounts
and my name was O'Malley -- I would not be amused if it saved
my name as O Malley...

If the user want's to input a ' and it makes sense, then allow the
user to do so. You shouldn't modify data before it is inserted
into the database if the data is valid in the given domain. (eg:
` is not valid in numbers, obviously )



--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
[url]http://www.upscene.com[/url]
Database development questions? Check the forum!
[url]http://www.databasedevelopmentforum.com[/url]

[Bill Karwin]

"Pat" <wfdvbf@dfg.cv> wrote in message
news:W7SdnbCqr7ggTkXeRVnygg@scarlet.biz...

> I made a form where visitors can introduce data.
> I use this:
> strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
> dat & ...)"
>
> My problem is that when someone introduces a quotation mark, i get a error
> and the insert fails (e.g. nam'e).

Quote characters can be escaped so that they can be used in SQL strings by
using the backslash (\) character. E.g. INSERT INTO mytable VALUES
('O\'Malley')
Also these characters can be encoded according to standard SQL as two quote
characters:
INSERT INTO mytable VALUES ('O''Malley')
See [url]http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html[/url].

Instead of filtering the string yourself, Perl and PHP offer API functions
to "encode" strings to be friendly to SQL syntax. In Perl DBI the function
is $dbh->quote($value), and in PHP it's mysql_escape_string().

See these pages for more information:
[url]http://www.oreilly.com/catalog/perldbi/chapter/ch04.html[/url] (for Perl DBI)
[url]http://us3.php.net/mysql_escape_string[/url] (for PHP)

The other method (which Martijn mentioned) is to send string values as
parameters to a prepared statement. See this article for more information:
[url]http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html[/url]
When sending strings as parameters, you don't need to escape special
characters.

Regards,
Bill K.

[Terry]

Pat wrote:

> Hi,
>
> I made a form where visitors can introduce data.
> I use this:
> strsql="INSERT INTO mytable (field1,field2 ...) values('" & lol & "',#" &
> dat & ...)"
>
> My problem is that when someone introduces a quotation mark, i get a error
> and the insert fails (e.g. nam'e).
>
> How can i prevent that? Controling each entered character seems me to be a
> very big work ...

$field1=eregi_replace("[^-a-z0-9.() ,]*","",$field1);
is one way that works. That will remove any character except an
alphanumeric, dash (-), period (.), parenthesis (()), space ( ), or
comma (,). Adjust as you see fit.

See also stripslashes().

HTH

--
TK
[url]http://www.wejuggle2.com/[/url]
Still Having a Ball


A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet?






..

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
[url]http://www.newsfeeds.com[/url] The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Jim Michaels]

"Martijn Tonies" <m.tonies@upscene.removethis.com> wrote in message
news:11tid05f5sgjd8a@corp.supernews.com...

>> Can't you use the replace function (replacing the quotation mark with
>> an empty string).
>>
>> strsql = "INSERT INTO mytable (field1) values (replace(" & lol & ",
>> char(39), ''))"

>
> Well, although that might solve the quote problem, it does change
> the data being saved.
>
> For example, if this was an application that keeps user accounts
> and my name was O'Malley -- I would not be amused if it saved
> my name as O Malley...

mysql_escape_string() and mysql_real_escape_string() saves the ' and " but
escapes them with a preceding \
BUT it doesn't handle ; so you will have to strip that one out yourself.
all these are used with SQL INJECTION, something you don't want, so I
suggest you escape your incoming data string variable.

>
> If the user want's to input a ' and it makes sense, then allow the
> user to do so. You shouldn't modify data before it is inserted
> into the database if the data is valid in the given domain. (eg:
> ` is not valid in numbers, obviously )
>
>
>
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> [url]http://www.upscene.com[/url]
> Database development questions? Check the forum!
> [url]http://www.databasedevelopmentforum.com[/url]
>
>
>

[Unregistered]

It saves it fine, but then on retrieval or display, php displays the "" as quetion marks(?) any help on display