Re: Apostrophe in SQL string

From: Robbe Morris (info_at_turnkeytools.com)
Date: 02/07/05 

Date: Sun, 6 Feb 2005 22:03:14 -0500

I agree with the poster below about "never" doing this.

For a variety of reasons, you have just opened up a major
security hole in your application even if you successfully
convert the apostrophes. You never know which "smart guy"
will start playing with your application just to prove how smart
they are and how dumb you are. You'd be surprised at the
number of "second cousins" that employees let play with
their work software/web sites.

The security hole you open up with dynamic sql strings would
be a cake-walk for any 12 year old who has an ounce of sense
about programming.

Is this the person you want to introduce to your boss as the
one who crashed your app or more likely deleted your data?

My intent is not to hurt your feelings, abuse you, or talk down
to you. Just to give a wake up call to someone I'm sure works
their tail off to build great software. I'd hate to see you get
embarrased.

I understand some applications may need to have different
update or insert statements for different situations. In these
cases, you could include an extra parameter in your
stored procedure that dictates which statements to process.
Include all possible input parameters and ensure that those
that are optional can be set as null or default values if they
aren't passed in. 

-- 
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net
"Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in message 
news:uEeUnIjCFHA.2156@TK2MSFTNGP10.phx.gbl...
> Miha,
> I wonder the same thing, I guess the answer is that we have not been able 
> to
> come up with a good solution in this space. Do you have any suggestions? I
> would be happy to pass them on.
>
> -- 
> Angel Saenz-Badillos [MS] Managed Providers
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:e0O4TNKBFHA.1992@TK2MSFTNGP10.phx.gbl...
>> Hi Angel,
>>
>> I hope you have this answer in "favorites answers" as it will have to be
>> repeated many times :-)
>> OTOH, I can imagine a scenario where you might have to pass in a
> dynamically
>> built WHERE clause instead of parameters:
>> IN clause is a very tempting candidate - when there is too many
> parameters.
>> There are workarounds, but they are all "not that easy" to employ.
>>
>> As this is very annoying (dealing with parameter lists that is) I can 
>> only
>> wonder why there is (still) no support for parameter lists even in Yukon.
>> Can you shed some light on this?
>>
>> -- 
>> Miha Markic [MVP C#] - RightHand .NET consulting & development
>> SLODUG - Slovene Developer Users Group
>> www.rthand.com
>>
>> "Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in 
>> message
>> news:un$9dGKBFHA.1564@TK2MSFTNGP09.phx.gbl...
>> > This just cannot be repeated enough times.
>> > DO NOT  pass in customer data directly into a Sql Query. ALWAYS use
>> > parameters.
>> >
>> > -- 
>> > Angel Saenz-Badillos [MS] Managed Providers
>> > This posting is provided "AS IS", with no warranties, and confers no
>> > rights.Please do not send email directly to this alias.
>> > This alias is for newsgroup purposes only.
>> > I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
>> >
>> >
>> >
>> >
>> > "Guest" <Guest@aew_nospam.com> wrote in message
>> > news:eGBVebFBFHA.3336@TK2MSFTNGP11.phx.gbl...
>> >> You've probably solved this by now. All you have to do is add another
>> > apostrophe beside the one you'd like to store e.g. insert into table 
>> > XYZ
>> > (Firstname) values ('D''Artienne').
>> >>
>> >> So in the table the column Firstname stores the value D'Artienne.
>> >>
>> >> Cheers
>> >> T
>> >>
>> >>
>> >> > Hi,
>> >> >
>> >> > in my application I add personal information about customers to a
>> > database
>> >> > by sending a SQL string. Now, sometimes there are customers with
> names
>> > that
>> >> > contain an apostrophe (like D'Artienne, etc.). Now, when I create a
> SQL
>> >> > string with such an apostrophe it gets malformatted (like: "insert
> into
>> >> > table1 (Firstname) values ('D'Artienne')" ). So, is there any way to
>> > insert a
>> >> > name that contains an apostrophe into a table by SQL?
>> >> >
>> >> >
>> >> > Thanks a lot
>> >> > peter
>> >>
>> >> User submitted from AEWNET (http://www.aewnet.com/)
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: Apostrophe in SQL string
    ... This alias is for newsgroup purposes only. ... >> DO NOT pass in customer data directly into a Sql Query. ... >> Angel Saenz-Badillos Managed Providers ... sometimes there are customers with ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Foxpro sql limitations
    ... My application is an online user interface for customers to log on to ... and check the status of their fleet of vehicles (the client is a fleet ... The ASP site connects to the Foxpro database used ... This SQL db has since been expanded to include some more ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: Modifying Data Directly through Studio Manager
    ... 'tool' to allow your customers to manage their data. ... I've seen people comment on turned on the query pane, ... Many of my customers are starting to upgrade to SQL 2005, ... Isaac Shloss, MCSE ...
    (microsoft.public.sqlserver.tools)
  • Re: OO vs. RDB challenge
    ... Lets say you have a list of customers ... Layer 1. ... SQL might help with Layer 1 ... SQL might help with Layer 2 but only if the query is simple enough. ...
    (comp.object)
  • Re: Serious errors with Create view command
    ... Either you open each view as needed with a different alias and assign this ... stored in the database, ... Edit Stored Procedures opens a code editing window and saves the code you ... can't paste it INTO the view designer sql view window as that's Read ...
    (microsoft.public.fox.helpwanted)
Loading