Need a Strategy to store the Single Quotes in the Database

[Solution Seeker]

I want to Store the String value with Single Quotes in the Field of
Database where if i try to Store the String value with Single Quotes
(as it is) then it is throwing the error as SQL String Truncated.

so we need a solution to store and retrieve user Entered value along
with single quotes into the Database.

i am using the String variable to frame the Qry(that is then passed
to Database for execution) which is as follows

StrSql="Insert into tblname values('" & txtfieldname.Te xt & "')"

for eg. if txtfieldName.te xt is "Mani's Test"

i want to store "Mani's Test" in the Field of Database
and on the same time i want to retrieve it as same

So let me know if u have any solutions / suggestions

thanks in advance

[Cor Ligthert]

Solution seeker,

Why don't you use parameters,

This is a sample for OleDb there is as well a simple sample for SQL on our
site.

http://www.windowsformsdatagridhelp....3-eb8b44af0137

I hope this helps,

Cor

[Mythran]

"Solution Seeker" <So************ @discussions.mi crosoft.com> wrote in
message news:9D******** *************** ***********@mic rosoft.com...

I want to Store the String value with Single Quotes in the Field of
Database where if i try to Store the String value with Single Quotes
(as it is) then it is throwing the error as SQL String Truncated.

so we need a solution to store and retrieve user Entered value along
with single quotes into the Database.

i am using the String variable to frame the Qry(that is then passed
to Database for execution) which is as follows

StrSql="Insert into tblname values('" & txtfieldname.Te xt & "')"

for eg. if txtfieldName.te xt is "Mani's Test"

i want to store "Mani's Test" in the Field of Database
and on the same time i want to retrieve it as same

So let me know if u have any solutions / suggestions

thanks in advance

You should use parameterized queries instead of direct SQL manipulation when
using values that are entered by a user...good example of why is as follows:

You have a field, txtFieldName.
You have the following code to insert values:
StrSql = "Insert into tblname values('" & txtfieldname.Te xt & "')"

I enter the following into the field:
');delete from tblname;

Or even worse, you don't have tightened security for the user that account
that accesses SQL Server, and I enter the following code:
');exec sp_addlogin 'someuserid','p wd';go;exec sp_addsrvroleme mber
'someuserid','s erveradmin'
You'd be in a lot of trouble.. what this would do is...

Insert blank value into tblName.
Creates a new userid named someuserid with a password of pwd.
Adds this new user to the serveradmin role (same role that user id 'sa' is a
member of).

Now the user who you thought was just inserting values into tblname of a
single database has now comprimised your system. They have admin access to
your entire SQL Server, and if you have granted SQL Server permission to
other areas of your file system, the user can enter T-SQL commands to
manipulate and even create executable files on your server...all because of
using unsafe sql to insert a value into the database....

So, most developer's that are aware of this and use dotnet will suggest you
to use parameterized queries. They are really easier to use and a lot
easier to understand :)

I'll go even further and suggest using Stored Procedures to do the
inserts/updates and call the stored procedures instead of building SQL
strings that are parameterized. That's even better IMO :)

HTH,
Mythran

[Dennis]

I assume this problem is also avoided is one uses DataSets to enter the
information then the DataAdapter.Upd ate method to update the database. Is
this correct?
--
Dennis in Houston
"Mythran" wrote:

"Solution Seeker" <So************ @discussions.mi crosoft.com> wrote in
message news:9D******** *************** ***********@mic rosoft.com...

I want to Store the String value with Single Quotes in the Field of
Database where if i try to Store the String value with Single Quotes
(as it is) then it is throwing the error as SQL String Truncated.

so we need a solution to store and retrieve user Entered value along
with single quotes into the Database.

i am using the String variable to frame the Qry(that is then passed
to Database for execution) which is as follows

StrSql="Insert into tblname values('" & txtfieldname.Te xt & "')"

for eg. if txtfieldName.te xt is "Mani's Test"

i want to store "Mani's Test" in the Field of Database
and on the same time i want to retrieve it as same

So let me know if u have any solutions / suggestions

thanks in advance

You should use parameterized queries instead of direct SQL manipulation when
using values that are entered by a user...good example of why is as follows:

You have a field, txtFieldName.
You have the following code to insert values:
StrSql = "Insert into tblname values('" & txtfieldname.Te xt & "')"

I enter the following into the field:
');delete from tblname;

Or even worse, you don't have tightened security for the user that account
that accesses SQL Server, and I enter the following code:
');exec sp_addlogin 'someuserid','p wd';go;exec sp_addsrvroleme mber
'someuserid','s erveradmin'
You'd be in a lot of trouble.. what this would do is...

Insert blank value into tblName.
Creates a new userid named someuserid with a password of pwd.
Adds this new user to the serveradmin role (same role that user id 'sa' is a
member of).

Now the user who you thought was just inserting values into tblname of a
single database has now comprimised your system. They have admin access to
your entire SQL Server, and if you have granted SQL Server permission to
other areas of your file system, the user can enter T-SQL commands to
manipulate and even create executable files on your server...all because of
using unsafe sql to insert a value into the database....

So, most developer's that are aware of this and use dotnet will suggest you
to use parameterized queries. They are really easier to use and a lot
easier to understand :)

I'll go even further and suggest using Stored Procedures to do the
inserts/updates and call the stored procedures instead of building SQL
strings that are parameterized. That's even better IMO :)

HTH,
Mythran