Inserting data with an Apostrophe

[Opie18]

Here is what i have


sDesc = Damon's
insert into tblAccount (Description)
values (sDesc);

My sql may not be right but how do i put the quotes around sDesc so that I won't get an erro with sql server...

Thanks,

John

 

[DotNetGnat]

Try this:

sDesc = Damon's
sDesc=Replace(sDesc,"'","''")

-DNG

 

[vongrunt]

Simply replace one apostrophe (') with two ('').

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Opie18]

will your replies allow the desc to go into the database with just the single quote or apostrope?? That is how it needs to go in...

so you know this is being inserted from VBA Code that was origianly going to access and we just migrated to SQL Server over the weekend

Thanks,

John

 

[jbenson001]

By the examples above, the data will go in as Damon's.

 

[vongrunt]

Whatever you do, final SQL statement must look like this:

insert into tblAccount (Description)
values ('Damon''s')

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[chiph]

Doubling up the apostrophes will work in the short term, but longer term is likely to cause significant problems.

Google on "SQL Injection".

It's an attack whereby the bad guy is able to execute arbitrary commands against your database server, and in some cases, against the machine the database server is running on (things like formatting the C: drive, dropping tables, exporting data, etc.).

This FAQ was written for VB6, but the techniques are also applicable to the .net languages:
faq709-1526

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[vongrunt]

> Doubling up the apostrophes will work in the short term, but longer term is likely to cause significant problems.

How? Assuming that developer always doubles single quotes.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Opie18]

Thank you

 

[chiph]

How? Assuming that developer always doubles single quotes.

Assume you have a name/address form for the user to enter their contact info. Pretend I'm an evil user, and I enter this into the name field:

[tab]';DROP TABLE tbl_User --

After your application code doubles up the quotes, and stuffs it into your dynamic SQL, it looks like:

[tab]INSERT INTO tbl_User (username, addr, city)
[tab]VALUES (''';DROP TABLE tbl_User -- ',
[tab]'501 Main Street', 'Smallville')

You now have three single-quotes in a row, which evaluate to two single-quotes, meaning an empty string. The semi-colon SQL terminator then kills the INSERT statement. And then it runs the DROP TABLE command. The double dash turns everything after it into a comment to ensure the DROP statement works.

Your Users table is now gone forever. Hope you had a backup.

It gets worse, too. You can run arbitrary commands from the SQL server by running the xp_cmdshell stored procedure. Stuff like the FORMAT command. Or the FTP command, which would allow them to get a copy of any file on your system. Or replace a file on your system with one of their own choosing (Gee, the bad guys just replaced Explorer.exe with their "improved" copy).

This is why parameterizing your queries is so important, and just doubling the quotes up does so little in the way of security.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[JamesLean]

You now have three single-quotes in a row, which evaluate to two single-quotes

No, the first quote is the first string delimiter, then the the next two are interpreted as a single quote within the string. So for your example you would get the value [tt][red]"[/red]';DROP TABLE tbl_User -- [red]"[/red][/tt] inserted in the table (without the red double quotes obviously).

--James