By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 2,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Need a Strategy to store the Single Quotes in the Database

P: n/a
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.Text & "')"

for eg. if txtfieldName.text 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
Nov 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 21 '05 #2

P: n/a

"Solution Seeker" <So************@discussions.microsoft.com> wrote in
message news:9D**********************************@microsof t.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.Text & "')"

for eg. if txtfieldName.text 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.Text & "')"

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','pwd';go;exec sp_addsrvrolemember
'someuserid','serveradmin'
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

Nov 21 '05 #3

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

"Solution Seeker" <So************@discussions.microsoft.com> wrote in
message news:9D**********************************@microsof t.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.Text & "')"

for eg. if txtfieldName.text 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.Text & "')"

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','pwd';go;exec sp_addsrvrolemember
'someuserid','serveradmin'
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

Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.