SQL Server Performance

How to insert data that includes ticks ', / , etc

Discussion in 'General Developer Questions' started by Zambratp, Jul 25, 2003.

  1. Zambratp New Member

    I have a dynamic insert statement....and on occasions the data includes ticks, or (), or / ...my insert fails at that point...what data type or table options can I use to make SQL SERVER ignore that field ( in other words treat it as just symbols) and do the insert?[|)]
  2. bambola New Member

    Are you replacing quotes before passing the string to the database? this I suspect is the cause of your problem.

  3. Zambratp New Member

    Im not sure I understand...

    I have the parameter set up so that the actually insert
    looks like ....Values ( 'values', 'values')...
    what happened was Insert ended up looking like this because the data had a leading
    tick Values (''values', 'values)
  4. gaurav_bindlish New Member

    I think Zambratp is asking about escape characters in SQL Server to avoid characters in the input being identified as SQL Server keywords.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. Zambratp New Member

    Im sorry I just found the right word.....

    Sometimes my data may have symbols that SQL SERVER 2000 uses as delimiters..

    So when my dynamic insert into statement is created I have something like this

    Insert into table ('col', 'col') values (''dog'),('cat')

    See in front of dog is an extra tick BUT that is good data...I need that to go in as data...but this doesnt always occur at the front...or at all..
  6. Zambratp New Member

    Let me clarify...
    (''dog'), ('cat') that is 2 single ticks in front of dog...one of the ticks is data the other is the SQL delimiter..
  7. bambola New Member

    you have to double the quotes here.
    Values (''values', 'values')
    will look like this
    Values ('''values', 'values')

    If you are constructing the string from VB/VBScript, you can use Replace(variable, "'", "''")
    Is that what you needed?

  8. Zambratp New Member

    We're using C++ API to build the insert statement...
    Can I use the double " all the time even if the data for the next insert does not have
    any delimiters?

    Insert into table ( 'col', 'col') values ('"values"'), 'values')

    will the REPLACE function add any extra characters/symbols?

    Sorry maybe I am not very clear with this problem..
  9. bambola New Member

    I don't remember the C++ syntax. The idea is to replace all the single quote in a string with 2x single quote.<br />You can use it no matter if a single quote exists, and it will not add anything else. If things are still not clear, feel free to ask <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Bambola.
  10. Zambratp New Member

    Thanks...Ill try that ....
  11. rushmada New Member

    U can use "" for this for ex as below


  12. Twan New Member

    Just beware of the dangers with Dynamic SQL... Dynamic SQL are a heaven for hackers... Search on "SQL Injection" in something like Google to get a huge amount of information about how to use Dynamic SQL to gain control of a database, server or entire domain...
  13. bambola New Member

    You are right about that, Twan. Dynamic SQL is something I would avoid at all cost for security and performance reasons. It is better to wrap your code within a stored procedure and give permissions to the login you are using (which should never ever be sa) to run this sproc.


Share This Page