Do not allow symbols in query?

[eK3eKyToPa]

A form in the site inserts new line in the DB, but need if some of the elements includes some symbols like :;.!@#$%^&*... to say that there is an error
Thanks!

[eK3eKyToPa]

* And if there is space between the word - error too.

[rocknbil]

Your symbols should insert just fine, if there is a problem, it will most likely be in quotes.

To properly insert or update text values, the select statement must quote the values, as in

insert into my_table 'some value';

So if some value is

She said 'do it now' and stormed away.

the select statement encounters the first set of quotes and presumes the statement ends there

insert into my_table 'She said 'do it now' and stormed away';

Everything after the first set of quotes is interpreted as a malformed statement. What has to happen is the quotes should be doubled in the incoming text:

insert into my_table 'She said ' 'do it now' ' and stormed away';

The same is true if the select statement is quoted using double quotes. "

insert into my_table "She said " "do it now" " and stormed away";

[jtara]

What has to happen is the quotes should be doubled in the incoming text

That's not a fix to this problem. It's a form - the user can enter anything they want.

The proper fix is to "escape" the string. This allows the characters to be inserted literally into the database as entered.

Every database has some means of specifying that a given character should be taken literally, rather than used as a lexical element of the SQL statement. For example, \" might represent a literal quote.

Now, you can't expect your users to enter \" when they want a quote, and the bad guys certainly won't follow your instructions!

I can't give you the specifics, since I don't know what scripting language, database, and database adapter you are using. But there is usually a function you can apply to data you get from a form to "escape" it and make it safe to insert into the database. It will apply your database's "escape" mechanism to any special characters in the input.

[rocknbil]

But I use it all the time. If you you single quotes in your select statement, you can sub out single quotes. If you use doubles, you sub out the double quotes. Escaping is just Another Way To Do It.

$formvalue =~ s/''/'/g;
insert into table '$formvalue';

$formvalue =~ s/""/"/g;
insert into table "$formvalue";

[dev.mysql.com...]

There are several ways to include quote characters within a string:

A “'” inside a string quoted with “'” may be written as “''”.
A “"” inside a string quoted with “"” may be written as “""”.
Precede the quote character by an escape character (“\”).
A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.

[jtara]

[quote] But I use it all the time. If you you single quotes in your select statement, you can sub out single quotes. If you use doubles, you sub out the double quotes. Escaping is just Another Way To Do It.

$formvalue =~ s/''/'/g;
insert into table '$formvalue'; ]/quote]

Well, that's different. You're actually manipulating the data you got from the form.

BTW, just escaping quotes isn't enough. You need to escape \x00, \n, \r, \, and \x1a (tab).

Might as well use the proper escape function.

Not that MySQL takes care of escaping when you use MySQL's placeholder capability. But may not be so for other databases. In order to take advantage of this, you MUST use MySQL placeholders, not construct a query string yourself from bits of SQL and data from the form.

In PHP:

[us3.php.net...]