Update column containing a quote
-
How can I update a column w/ a QUOTE in the persons name?
UPDATE
Customers SET Last_Name = 'O'CONNOR' WHERE Cust_ID = 'A34778'
BT -
Double the quotes (2 single quotes >> 2 characters) :
UPDATE Customers SET Last_Name = 'O''CONNOR' WHERE Cust_ID = 'A34778'
-
Also the use of a stored proc would completely void the need for this :
CREATE PROCEDURE dbo.Demo @LastName AS VARCHAR(30), @CustID AS VARCHAR(10)
AS
SET NOCOUNT ON
UPDATE dbo.Customers SET Last_name = @MyParam WHERE Cust_ID = @CustID
SET NOCOUNT OFF
GO
-
I'll second Ninja's suggestion of using a stored proc. Building applications that build sql string from data (any data) introduces a very significant security flaw.
Some examples:
User input expects username/password user enters admin'-- for username nothing for password
SELECT * from accounts where username = 'admin'--' and Password = ''
Just doublequoting everythign won't work to protect you either (especially in a weak-typed language like vbscript asp)
User input expects a number, user enters 5;TRUNCATE TABLE MyReallyImportantData --
SELECT * FROM groupData WHERE group = 5;TRUNCATE TABLE MyReallyImportantData -- and session = 32
SQL guy and Houston Magician
-
UPDATE Customers SET Last_Name = REPLACE(@LastName,'''','''''') WHERE Cust_ID = 'A34778'
Do heed the other's warnings about SQL Injection attacks...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Since when do we need to use replace on parameters when not using dynamic sql?
And how would the param be set in the first place?
-
SET QUOTED_IDENTIFIER OFF
SELECT "Now ' you can use '' quotes ' in your '' strings"
SET QUOTED_IDENTIFIER ON
-
Why change that setting when you don't have too???
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply