Single Quote In A Text Field

  • Thread starter Thread starter Jeff Gaines
  • Start date Start date
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.
 
Use parametrised sql statements and thus avoid sql injection attacks at the
same time.
 
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!
 
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.
 
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 :-)
 
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
 
Every database is subject to SQL injection attacks (as long as it supports
SQL of course) when not used correctly.
 
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...
 
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...
 
The level of vulnerability may vary but I think we agree on the fact that
when there is SQL than sql injection is possible.
 
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.
 
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...
 
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?
 
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)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top