SP inserts into table

Question

Post reply

SP inserts into table

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
September 20, 2016 at 9:05 am

#313804

Based on this below SP.How to know this sp is updating or inserting into a specific table?
Do we need to specify table name here?
CREATE PROCEDURE [dbo].[sampleLog_Insert]
@table varchar(30),
@date varchar(30),
@file varchar(255)
AS
BEGIN
SET QUOTED_IDENTIFIER OFF
declare @query nvarchar(500)
Set @query='UPDATE ' + @table + ' SET DateTimeFile="'+@date+'",NameFile="'+@File+'" Where NameFile is Null'
exec sp_executesql @query
RETURN
END
GO

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The table name is specified as a parameter

@table varchar(30)

That is horrible design and a security nightmare. Completely vulnerable to SQL Injection since no checks are done on the parameters. Terrible code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

mcfarlandparkway (9/20/2016)
Based on this below SP.How to know this sp is updating or inserting into a specific table?
Do we need to specify table name here?
CREATE PROCEDURE [dbo].[sampleLog_Insert]
@table varchar(30),
@date varchar(30),
@file varchar(255)
AS
BEGIN
SET QUOTED_IDENTIFIER OFF
declare @query nvarchar(500)
Set @query='UPDATE ' + @table + ' SET DateTimeFile="'+@date+'",NameFile="'+@File+'" Where NameFile is Null'
exec sp_executesql @query
RETURN
END
GO

I agree 100% with Gail. Terrible code!

How can you tell if the query is Updating or Inserting into a specific table? The dynamic SQL is only written to handle updates. Update never inserts new values into a table. The only way the posted code will do an insert is if it falls victim to a SQL Injection attack.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 3

I completely agree.This is very horrible code. which was written by old people.

Even I wonder after seeing this code..

John Rowan SSC Guru Points: 56440 More actions · Answer 4

mcfarlandparkway (9/20/2016)
I completely agree.This is very horrible code. which was written by old people.
Even I wonder after seeing this code..

Old people should not be allowed to write SQL code...:-D

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Leo.Miller SSChampion Points: 13048 More actions · Answer 5

Criticism of poor code and old people aside, the only way to know what table is being referenced is to run a trace (Profiler or background) to capture the incoming parameters. RPC Start would do it.

As already mentioned it must be doing an UPDATE as that is all that can be generated from this code baring SQL Injection.

And yes, it is terrible code, but actually some of us old people aren't the problem. I'm seeing a lot of code like this on client sites being written by youngsters with "T-SQL For Dummies" or "T-SQL in 21 Days" under the belt who think they know how to write code. If I saw a developer doing this he'd be two steps short of a DCM.

My favourite XKCD for DBAs https://www.xkcd.com/327/

Please don't flame us old people.

Leo

22+ years in MS-SQL Server and not a spring chicken when I got into it.

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

mcfarlandparkway (9/20/2016)
I completely agree.This is very horrible code. which was written by old people.
Even I wonder after seeing this code..

Santa is w-a-t-c-h-i-n-g! 😉 Us "old people" wouldn't write code in such a manner.

--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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

John Rowan (9/20/2016)
mcfarlandparkway (9/20/2016)
I completely agree.This is very horrible code. which was written by old people.
Even I wonder after seeing this code..
Old people should not be allowed to write SQL code...:-D

Ok... how would everyone like to tell their mama's that some old dude just kicked their a$$? 😛

--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)

John Rowan SSC Guru Points: 56440 More actions · Answer 8

Jeff Moden (9/20/2016)
John Rowan (9/20/2016)
mcfarlandparkway (9/20/2016)
I completely agree.This is very horrible code. which was written by old people.
Even I wonder after seeing this code..
Old people should not be allowed to write SQL code...:-D
Ok... how would everyone like to tell their mama's that some old dude just kicked their a$$? 😛

Agreed! I'm not 'old', but in in our industry, I sure feel it. I can run circles around most of these young bucks.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ken McKelvey SSCoach Points: 18768 More actions · Answer 9

As mentioned, this is an horrible design.

If you have to live with an app which uses it, I would alter it so the parameters are logged and a check made that the table exists.

(You should also add exception handling etc)

In outline:

-- Create logging table

-- Something like

--CREATE TABLE dbo.SampleLogInsertParams

--(

--Id int IDENTITY NOT NULL

--CONSTRAINT PK_SampleLogInsertParams PRIMARY KEY

--,InsertTime datetime NOT NULL

--CONSTRAINT DF_InsertTime DEFAULT(CURRENT_TIMESTAMP)

--,PTable sysname NOT NULL

--,PDate date NOT NULL

--,PFile varchar(255) NOT NULL

--);

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sampleLog_Insert]

@table sysname,

@date date,

@file varchar(255)

AS

SET NOCOUNT ON;

DECLARE @query nvarchar(500);

INSERT INTO dbo.SampleLogInsertParams(PTable, PDate, PFile)

VALUES(@table, @date, @file);

-- alter if more than one schema

SELECT @query = N'UPDATE ' + TABLE_SCHEMA + N'.' + TABLE_NAME + N' SET DateTimeFile = @date, NameFile = @file WHERE NameFile IS NULL'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = @table;

EXEC sp_executesql @query, N'@date date, @file varchar(255)', @date, @file;

GO

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

Ken McKelvey (9/21/2016)
As mentioned, this is an horrible design.
If you have to live with an app which uses it, I would alter it so the parameters are logged and a check made that the table exists.
(You should also add exception handling etc)
In outline:
-- Create logging table
-- Something like
--CREATE TABLE dbo.SampleLogInsertParams
--(
--Id int IDENTITY NOT NULL
--CONSTRAINT PK_SampleLogInsertParams PRIMARY KEY
--,InsertTime datetime NOT NULL
--CONSTRAINT DF_InsertTime DEFAULT(CURRENT_TIMESTAMP)
--,PTable sysname NOT NULL
--,PDate date NOT NULL
--,PFile varchar(255) NOT NULL
--);
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sampleLog_Insert]
@table sysname,
@date date,
@file varchar(255)
AS
SET NOCOUNT ON;
DECLARE @query nvarchar(500);
INSERT INTO dbo.SampleLogInsertParams(PTable, PDate, PFile)
VALUES(@table, @date, @file);
-- alter if more than one schema
SELECT @query = N'UPDATE ' + TABLE_SCHEMA + N'.' + TABLE_NAME + N' SET DateTimeFile = @date, NameFile = @file WHERE NameFile IS NULL'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @table;
EXEC sp_executesql @query, N'@date date, @file varchar(255)', @date, @file;
GO

MUCH safer.

I'm also thinking that the OP should include the schema name in the input parameters, as well.

--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)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

Ken McKelvey (9/21/2016)
As mentioned, this is an horrible design.
If you have to live with an app which uses it, I would alter it so the parameters are logged and a check made that the table exists.
(You should also add exception handling etc)

As a side note. If you're logging the parameters, you should also log the user and a timestamp for future reference, IMHO.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2