Single Quote In A Text Field

J

Jeff Gaines

In my efforts at learning ADO.NET I have discovered that it balks at text
fields with single quote marks embedded. The field in question comprises
notes that can contain anything, it is stored as a memo field in Access.

I tried to wrap the whole field in double quotes without success, it still
balked at the first single quote and broke my insert statement.

Is there an easy way to handle this? If not I'll have to write a routine
to strip out single quotes before saving the field.
 
M

Miha Markic

Use parametrised sql statements and thus avoid sql injection attacks at the
same time.
 
J

Jeff Gaines

Use parametrised sql statements and thus avoid sql injection attacks at
the same time.
Click to expand...

Many thanks, Miha :)

I am very new to this so I had to look some of the words up but I have
amended the app and it works a treat now!
 
W

WenYuan Wang [MSFT]

Hello Jeff,
Thanks for Miha's reply.

In ADO.Net world, OleDbParameter is invited to address such issue.
We suggest you use parametrised SQL statements.
By this way, we also could avoid SQL injection attacks.

For example:
//init
System.Data.OleDb.OleDbConnection cn = new
System.Data.OleDb.OleDbConnection();
cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\test.mdb";
System.Data.OleDb.OleDbCommand cmd=new
System.Data.OleDb.OleDbCommand();
cmd.Connection = cn;
cmd.CommandText="insert into Table1(field3) values (?)";
cmd.CommandType=System.Data.CommandType.Text;
cmd.Parameters.Add("@field3",
System.Data.OleDb.OleDbType.VarWChar);

//insert row
cmd.Parameters["@field3"].Value = "test1'test2\"test3";
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

Hope this help. Please feel free to update here, if you have anything
unclear. We are glad to assist you.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
J

Jeff Gaines

Hello Jeff,
Thanks for Miha's reply.
Click to expand...

Yes indeed :)
In ADO.Net world, OleDbParameter is invited to address such issue.
We suggest you use parametrised SQL statements.
By this way, we also could avoid SQL injection attacks.
[snipped]

Hope this help. Please feel free to update here, if you have anything
unclear. We are glad to assist you.
Click to expand...

It certainly helps enormously :)

I had 2 problems.

The first is that the app I chose to convert to ADO.Net uses a couple of
tables each with lots of fields, many of which aren't stored but
calculated. I know it's a minimal app to the professionals but it was the
wrong app to choose for this exercise. Anyway it's pretty well there now.

The second was a real brain fade. Having created functions to create
parameterised insert/update strings I decided they looked the same and
could be combined. Of course they are not the same but it took me a couple
of hours to realise that. I am back on track now.

I hope the old adage about learning from mistakes is true :)
 
C

Cor Ligthert[MVP]

Hi WenYuan,
By this way, we also could avoid SQL injection attacks.
Click to expand...

In an Access database? AFAIK it can not be reached ofline beside by a
webservice or something like that.

Although it is of course the way to go.

No need to respond.

:)

Cor
 
M

Miha Markic

Every database is subject to SQL injection attacks (as long as it supports
SQL of course) when not used correctly.
 
W

William Vaughn

Ah, I doubt it. SQL Server and Oracle that support multiple operations are
most vulnerable--SQL CE does not and is not subject to the same type of
attacks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Miha Markic said:
Every database is subject to SQL injection attacks (as long as it supports
SQL of course) when not used correctly.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cor Ligthert said:
Hi WenYuan,


In an Access database? AFAIK it can not be reached ofline beside by a
webservice or something like that.

Although it is of course the way to go.

No need to respond.

:)

Cor
Click to expand...
Click to expand...
 
C

Cor Ligthert[MVP]

Miha,

I am curious how you do that with an Access database. I assume that you
don't mean attacks from your own desktop or inside the local Lan, where an
Access database is normaly not used in a large organisation.

An access database is as far as I know only referencable by using its
destination by a phycical path and not an IP or DNS name?

However if you know a method to reach it in another way, I would very much
be pleased to see that. (Not by a webservice or something else that uses a
phycical path of course, because that is only giving back result on
methods).

Cor


Miha Markic said:
Every database is subject to SQL injection attacks (as long as it supports
SQL of course) when not used correctly.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cor Ligthert said:
Hi WenYuan,


In an Access database? AFAIK it can not be reached ofline beside by a
webservice or something like that.

Although it is of course the way to go.

No need to respond.

:)

Cor
Click to expand...
Click to expand...
 
M

Miha Markic

The level of vulnerability may vary but I think we agree on the fact that
when there is SQL than sql injection is possible.
 
W

WenYuan Wang [MSFT]

It's truly a great news.
I think you have learned much from mistakes.
If there is any other issue blocks you, feel free to post it in newsgroup
again.
You are always welcome. :)

Good Luck!

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
W

William Vaughn

If you look at the examples in Wikipedia, all of them assume that the SQL
engine can execute more than one operation per statement. JET (and SQLCe)
cannot.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Miha Markic said:
The level of vulnerability may vary but I think we agree on the fact that
when there is SQL than sql injection is possible.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

William Vaughn said:
Ah, I doubt it. SQL Server and Oracle that support multiple operations
are most vulnerable--SQL CE does not and is not subject to the same type
of attacks.
Click to expand...
Click to expand...
 
M

Miha Markic

William Vaughn said:
If you look at the examples in Wikipedia, all of them assume that the SQL
engine can execute more than one operation per statement. JET (and SQLCe)
cannot.
Click to expand...

Do you really think that SQL injection is impossible without multioperation
execution?
 
W

William Vaughn

You're right... since an UPDATE or INSERT could be perverted, that would
include any DBMS engine that accepts commands--SQL or not.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Quote Marks Question 2
Updating field with single/double quotes 6
escape single quote in MS SQL in .net framework 10
Count text values in a Access Field 0
using a single quote ' in SEARCH function 2
Single and Double Quote marks 2
Handling of Double or Single Quotation Marks in ConversationTopic 6
get rid of single quote in field from Access export 4

Top