"Solution Seeker" <SolutionSee***@discussions.microsoft.com> wrote in
message news:9D6F25F3-71BE-45B6-B5B4-6B456FD80EDF@microsoft.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

"Mythran" wrote:

>
> "Solution Seeker" <SolutionSee***@discussions.microsoft.com> wrote in
> message news:9D6F25F3-71BE-45B6-B5B4-6B456FD80EDF@microsoft.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
>
>