Re: Need a Strategy to store the Single Quotes in the Database


"Solution Seeker" <SolutionSeeker@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:9D6F25F3-71BE-45B6-B5B4-6B456FD80EDF@xxxxxxxxxxxxxxxx 
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

.

Relevant Pages

  • Re: return multiple rows from sql statement
    ... Concatening SQL ... strings from input values is almost certainly a safe path to SQL ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Establish connection with and transferring data to Access
    ... If you Google for SQL +"Insert into" you'll a dozen tutorials. ... The specific problem with your code is that you need to quote string ... Dim vConnection As New ADODB.Connection ... Doug's code opens and writes to an existing database. ...
    (microsoft.public.word.vba.general)
  • Re: Random Access Files in databases
    ... I am not a Database design guru either, but it might be wise to break ... location and read in in the string, and then you want to get an integer, so ... I'm pretty sure the SQL language does have ways to seek around in BLOB ... some SQL purist are quick to point out that MySQL doesn't implement the SQL ...
    (comp.lang.java.programmer)
  • Problems Setting Up Database
    ... I seem to be having errors creating and accessing an SQL database. ... New SQL Server Database'. ... System.Web.Management.SqlServices.ExecuteFile(String file, String server, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ADO exception with character combination inside string...
    ... quoted identifier not a string literal. ... The string being sent as SQL is composed like this: ... into stored procedures inside the MSSQL database. ... use calls to the stored procedures in order to update data or retrieve ...
    (borland.public.delphi.database.ado)