Re: Handling Quotes in Expression Builder

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I would much prefer to use parameters but have no idea how to.
I just saw a property line for the statement to execute and figured
that SSIS is just taking the value and sticking it into
sqlcommand.text. Don't know how to use the interface to add a
parameter.

thanks,


On Thu, 9 Mar 2006 04:33:28 -0800, Darren Green
<darren.green@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

My SQL statememt uses single quotes to delimit strings, so I need to escape
them when they are contained in the data string. The expression uses double
quotes, but nowhere do I have a luiteral double quote, so no worries.

My expression is this -

"INSERT Table VALUES('" + REPLACE(@[User::Data], "'", "''") + "')"

My variable value is this-

My silly string's have quotes, " double as well.

The evaluated result is-

INSERT Table VALUES('My silly string''s have quotes, " double as well.')


Using the parameter support of the Execute SQL Task would be safer and
possibly easier. String concatenation for SQL statements just means a risk of
SQL injection by default.
.

Relevant Pages

  • RE: Trying to Execute a file using exec master.dbo.xp_cmdshell
    ... I think it's got something to do with the quotes, ... Use profiler to see the actual quotes passed to SQL ... > the SQL Query analyzer (with the same user connection that the ASP ... > then execute it from ASP) ...
    (microsoft.public.sqlserver.programming)
  • RE: SQL Injection- Bypassing magic_quotes
    ... bypassing single quotes in your case should not be a problem. ... can encode strings in Microsoft SQL in a number of ways. ... if you want to encode the entire SQL ... Because i was trying to execute: ...
    (Pen-Test)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... Hard code WHERE clauses. ...
    (comp.lang.cobol)
  • Re: SQL Injection- Bypassing magic_quotes
    ... Because i was trying to execute: ... Don't terminate the query and you most ... Or try something like bobcat or one of the other SQL injection tools out ... Chief Information Security Officer ...
    (Pen-Test)
  • Re: using VBA to insert records from access database to sql server
    ... In SQL you 'escape' single quotes by doubling them. ... > Dim oConn As ADODB.Connection ... > Dim strInsert As String ...
    (microsoft.public.access.modulesdaovba)