Need list of character that can not be entered in sql database

[Manoj Kumar]

Hi,

Can anyone please tell me the full list of characters that can not be entered in sql server database. for example, single quotes, double quotes so on. I am really stuck with problem.

REgards,
Manoj

[Bob Barrows]

Post by Manoj Kumar
Hi,
Can anyone please tell me the full list of characters that can not be
entered in sql server database. for example, single quotes, double
quotes so on. I am really stuck with problem.

I cannot provide such a list, because there are no characters that cannot be
entered into a sql server database. Certainly both single quotes and double
quotes can be entered into a column with a character datatype.
Some characters have to be escaped when using t-sql to enter hard coded
literal strings containing them, but that is quite different from saying
they cannot be entered. The reason a character would have to be escaped by
doubling it is that the character has a meaning in t-sql that makes it
impossible to interpret it as a literal character unless it is escaped.
Single quotes are an example of this. Try this:

create table Names (
LastName varchar(20))

insert Names
values ('O''Grady')

SELECT LastName From Names

drop table names
go

See? It's certainly possible to enter a quote into the table.

[Gene Wirchenko]

On Tue, 4 Sep 2012 02:22:48 -0700 (PDT), Manoj Kumar

Post by Manoj Kumar
Can anyone please tell me the full list of characters that can not be

entered in sql server database. for example, single quotes, double
quotes so on. I am really stuck with problem.

Both sorts of quotes can be entered. Single-quotes have to be
repeated in literals, but they can be inserted.

use tempdb
go
create table #table (Value varchar(max) not null);
insert into #table
(Value)
values
('This shouldn''t fail.'),
('I said, "Hello."')
select * from #table

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Manoj Kumar
Can anyone please tell me the full list of characters that can not be
entered in sql server database. for example, single quotes, double
quotes so on. I am really stuck with problem.

There are of course tons of characters that cannot be entered in a
database. For instance, all characters that you can dream up yourself
and that are not in the Unicode standard cannot be entered. If we
constrain us to Unicode characters, you cannot faithfully add characters
outside the base plane, that is characters from U+10000 and up, unless
you are on SQL 2012 and have a surrogate-aware collations.

Single quotes can of course be entered with no pain. Since you mention
single quotes, I assume that you are doing some data cleansing to
prevent SQL injection, but in such case you are barking up the wrong
tree. You should make sure that all SQL that the client generates,
is parameterised, in which case there are no futher concernts.

[Manoj Kumar]

Thanks for your reply.

But the case is different. When try to insert a paragraph into sqlserver database say supposet

string query="insert into tables values('"&name&"','"&paragraph"&')"
execute query

if the variable paragraph contains single quotes or double quotes, then it show the error.

Post by Manoj Kumar
Hi,
Can anyone please tell me the full list of characters that can not be entered in sql server database. for example, single quotes, double quotes so on. I am really stuck with problem.
REgards,
Manoj

[Bob Barrows]

Post by Manoj Kumar
Thanks for your reply.
But the case is different. When try to insert a paragraph into
sqlserver database say supposet
string query="insert into tables values('"&name&"','"&paragraph"&')"
execute query
if the variable paragraph contains single quotes or double quotes, then it show the error.

I can see why single quotes would raise an error, but why double quotes? Are
you sure they are causing a problem? They certainly would not be causing a
problem with the database engine.

In any case, that's why you need to use parameters instead of dynamic sql.
What language is that? You'll need to ask in a forum or newsgroup focussed
on that language how to pass values to parameterized sql statements. It's
certainly possible in .Net and older versions of VB.

An alternative to using parameters is to escape the characters by doubling
them before concatenating them into your string. Here is what would be done
in vb/vbscript:

name = replace(name,"'", "''")
paragraph=replace(paragraph,"'","''")
query="insert into tables values('"&name&"','"&paragraph"&')"


For the solution related to your programming language, you need to find a
forum or newsgroup focussed on that language.

[Manoj Kumar]

Post by Bob Barrows

Post by Manoj Kumar
Thanks for your reply.
But the case is different. When try to insert a paragraph into
sqlserver database say supposet
string query="insert into tables values('"&name&"','"&paragraph"&')"
execute query
if the variable paragraph contains single quotes or double quotes,
then it show the error.

I can see why single quotes would raise an error, but why double quotes? Are
you sure they are causing a problem? They certainly would not be causing a
problem with the database engine.
In any case, that's why you need to use parameters instead of dynamic sql.
What language is that? You'll need to ask in a forum or newsgroup focussed
on that language how to pass values to parameterized sql statements. It's
certainly possible in .Net and older versions of VB.
An alternative to using parameters is to escape the characters by doubling
them before concatenating them into your string. Here is what would be done
name = replace(name,"'", "''")
paragraph=replace(paragraph,"'","''")
query="insert into tables values('"&name&"','"&paragraph"&')"
For the solution related to your programming language, you need to find a
forum or newsgroup focussed on that language.

i think this will work for me. Thanks.
Let me try it..

[Erland Sommarskog]

Post by Manoj Kumar
But the case is different. When try to insert a paragraph into sqlserver
database say supposet
string query="insert into tables values('"&name&"','"&paragraph"&')"
execute query
if the variable paragraph contains single quotes or double quotes, then it show the error.

YOU SHOULD NOT RUN QUERIES THIS WAY! Assuming that you use VB .Net, your
code shold go:

query="insert into tables values(@name, @paragraph)"
query.Parameters.Add("@name", SqlDbType.NVarChar, 50)
query.Parameters("@name").Value = name
query.Parameters.Add("@paragraph", SqlDbType.NVarChar, 50)
query.Parameters("@parapgraph").Value = name

Never build SQL strings by concatenating with variables!