T SQL Statment with apostrophe, %, # letters

Question

when I am writing an insert statmnet includes apostrophe, % or # it gaves me an error
any clue

---

Best Regards, Ammar MCT

Answer 1

Can you post the code you are using?

---

Abdallah, PMP, MCTS

Answer 2

when I am writing an insert statmnet includes apostrophe, % or # it gaves me an error
any clue

 

---

Best Regards, Ammar MCT

This works just fine.  Can you share how you are trying to do this?

create table #special_char_test
(
column1 varchar(10)
)
go

--insert single apostrophy
insert into #special_char_test values ('''');
go

--insert single percent sign
insert into #special_char_test values ('%');
go

--insert single pound sign
insert into #special_char_test values ('#');
go

select column1
  from #special_char_test
go

drop table #special_char_test
go

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 3

for example

insert

 

into DutyReportComment

values

 

(1,'my name's ahmed', 1)

---

Best Regards, Ammar MCT

Answer 4

As Phil wrote, just add 1 more apostrophe.

 

insert into DutyReportComment
values (1,'my name''s ahmed', 1)

Answer 5

for example

insert

 

into DutyReportComment

values

 

(1,'my name's ahmed', 1)

---

Best Regards, Ammar MCT

insert into DutyReportComment values (1,'my name''s ahmed',1);

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 6

how?
do you mean (1, 'my name''s ahmed' ,1)

---

Best Regards, Ammar MCT

Answer 7

Yes.

Answer 8

To add to all the answers; This is SQL Injection 101.  Your front-end deployment should be passing, parsing and preventing these types of situations from coming into SQL Server (or any database server). 

Utilize the string manipulation functions to handle the values before they get to SQL Server

---

Ted Krueger Blog on lessthandot.com @onpnt on twitter

Answer 9

but this will not work with me
I have an application, all users use to do data entry
they write this data from a screen,
the T SQL gives an eror whenever a user uses these letter
how can I control this

---

Best Regards, Ammar MCT

Answer 10

Refer to my reply below.  you have a serious application design flaw that cannot be handled on SQL Server side without awful trigger or event capture methods and would trash performance all around and make maintainability very difficult. 

---

Ted Krueger Blog on lessthandot.com @onpnt on twitter

Answer 11

My front end application is a VB coded
so you suggest that I have to manipulate the string before sending to SQL
but I really don't know how, for example the apostrophe, shall I add another apostrophe as phil said, but this will not English, shall I removed it, user may refuse

do you ideas
this is exactly what I was asking about if anyone had the same issue

---

Best Regards, Ammar MCT

Answer 12

VB.NET

stringvalue.Replace(''','''')

VB

Replace(stringvalue,''','''')

---

Ted Krueger Blog on lessthandot.com @onpnt on twitter

Answer 13

My front end application is a VB coded
so you suggest that I have to manipulate the string before sending to SQL
but I really don't know how, for example the apostrophe, shall I add another apostrophe as phil said, but this will not English, shall I removed it, user may refuse

do you ideas

---

Best Regards, Ammar MCT

You should be using placeholders in the VB code. 

Something like you see here, from Xiaoyun: http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/6bdf8b71-1cf1-41c0-848c-4fca2c9e1ea2

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 14

Question is moving outside the scope of the forum.

Ammar, I suggest you take nito account the development platform you have and the process flow.  Compile that into a new thread in the forum that fits it best

---

Ted Krueger Blog on lessthandot.com @onpnt on twitter