473,722 Members | 2,397 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need a Strategy to store the Single Quotes in the Database

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
Nov 21 '05 #1
3 2861
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

"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

Nov 21 '05 #3
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

Nov 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
8852
by: Maxim Vexler | last post by:
Hello to everyone, Assuming i have this simple script : <?PHP //Opening tag =' $html_header=' <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
7
22779
by: Leif B. Kristensen | last post by:
I'm working with a Python program to insert / update textual data into a PostgreSQL database. The text has single and double quotes in it, and I wonder: What is the easiest way to escape quotes in Python, similar to the Perlism "$str =~ s/()/\\$1/g;"? I tried the re.escape() method, but it escapes far too much, including spaces and accented characters. I only want to escape single and double quotes, everything else should be acceptable...
5
3604
by: Mad Scientist Jr | last post by:
Has anyone worked on code that that can parse evaluation expressions (could be numbers or strings) like ( ( "dog" = "dog" ) or "foo" = "bar" ) and ("cow" = "bat" and "bye" = "hi") or ("math" = "fun") or ( ( 1 = 5 ) or ( 2 < 3 ) ) and (1 <= 6)
15
4635
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to communicate with a MySQL database table on a web server, from inside of my company's Access-VBA application. I know VBA pretty well but have never before needed to do this HTTP/XML/MySQL type functions.
9
2113
by: Harold Crump | last post by:
Greetings, I have a fairly vanilla PHP web application that stores and retrieves data in a MySQL database. Users will be adding a lot of special characters such as single and double quotes, accented French characters, etc. I want to eliminate any potential for XSS or SQL injection attacks. My question - is it enough to pass all user input through the
43
2608
by: SLH | last post by:
hi people. im trying to validate input received via a text area on an ASP page before writing it to a database. i cant use client side javascript due to policy, so it all has to happen on the server. here is what i was trying, but pieces of it continue to break for one reason or another. the thinking behind this function was like this: if the input is less than 10 characters long, fail. if its 10 characters or greater, but it doesnt...
7
10538
by: nick.bonadies | last post by:
I'm trying to deal with user inputs of single quotes into form fields that get input into a MSSQL database. So far I have discovered that if I turn on magic_quotes_sybase in my php.ini file PHP will correctly escape the single quotes. The problem happens when I am trying to retrieve data from the database, PHP will try to comment out what it has already commented out, instead of stripping the extra single quote. So as an example, if...
5
5290
by: andrei.avk | last post by:
Hi, I'd like to store chunks of text, some of them may be very large, in a database, and have them searchable using 'LIKE %something%' construct. These pieces of text may have single and double quotes in them, I tried escaping them using re module and string module and either I did something wrong, or they escape either single quotes or double quotes, not both of these. So that when I insert that text into a db record, this causes an error...
2
6667
by: ylj798 | last post by:
this string from web by the Regular Expression, −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− href="#" onClick="ConvertURL2FG('Flashget:// W0ZMQVNIR0VUXWh0dHA6Ly9tb3YuM2dwLmNuL2d1aWxpbi8yMDA4LzExLzExL3l1ZWhvdWppZmVuMDIuM2dwW0ZMQVNIR0VUXQ==&233','', 233)" oncontextmenu="Flashget_SetHref(this)" fg="Flashget://...
0
8863
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
8739
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9384
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9157
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8052
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4502
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2602
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.