No, stored procedures do not prevent SQL injection. Here's an actual example (from an in-house app someone created where I work) of a stored procedure that unfortunately permits SQL injection:
This sql server code:
CREATE PROCEDURE [dbo].[sp_colunmName2]
@columnName as nvarchar(30),
@type as nvarchar(30),
@searchText as nvarchar(30)
AS
BEGIN
DECLARE @SQLStatement NVARCHAR(4000)
BEGIN
SELECT @SQLStatement = 'select * from Stations where '
+ @columnName + ' ' + @type + ' ' + '''' + @searchText + ''''
EXEC(@SQLStatement)
END
END
GO
roughly equivalent to postgres:
CREATE or replace FUNCTION public.sp_colunmName2 (
columnName varchar(30),
type varchar(30),
searchText varchar(30) ) RETURNS SETOF stations LANGUAGE plpgsql
AS
$$
DECLARE SQLStatement VARCHAR(4000);
BEGIN
SQLStatement = 'select * from Stations where '
|| columnName || ' ' || type || ' ' || ''''|| searchText || '''';
RETURN QUERY EXECUTE SQLStatement;
END
$$;
The developer's idea was to create a versatile search procedure, but the result is that the WHERE clause can contain anything the user wants, allowing a visit from little Bobby Tables.
Whether you use SQL statements or stored procedure doesn't matter. What matters is whether your SQL uses parameters or concatenated strings. Parameters prevent SQL injection; concatenated strings allow SQL injection.
Add a persistent calculated field that contains a CHECKSUM
on the 5 fields, and use that to perform the comparisons.
The CHECKSUM
field will be unique for that specific combination of fields, and is stored as an INT
that results in a much easier target for comparisons in a WHERE
clause.
USE tempdb; /* create this in tempdb since it is just a demo */
CREATE TABLE dbo.t1
(
Id bigint constraint PK_t1 primary key clustered identity(1,1)
, Sequence int
, Parent int not null constraint df_T1_Parent DEFAULT ((0))
, Data1 varchar(20)
, Data2 varchar(20)
, Data3 varchar(20)
, Data4 varchar(20)
, Data5 varchar(20)
, CK AS CHECKSUM(Data1, Data2, Data3, Data4, Data5) PERSISTED
);
GO
INSERT INTO dbo.t1 (Sequence, Parent, Data1, Data2, Data3, Data4, Data5)
VALUES (1,1,'test','test2','test3','test4','test5');
SELECT *
FROM dbo.t1;
GO
/* this row will NOT get inserted since it already exists in dbo.t1 */
INSERT INTO dbo.t1 (Sequence, Parent, Data1, Data2, Data3, Data4, Data5)
SELECT 2, 3, 'test', 'test2', 'test3', 'test4', 'test5'
WHERE Checksum('test','test2','test3','test4','test5') NOT IN (SELECT CK FROM t1);
/* still only shows the original row, since the checksum for the row already
exists in dbo.t1 */
SELECT *
FROM dbo.t1;
In order to support a large number of rows, you'd want to create an NON-UNIQUE index on the CK
field.
By the way, you neglected to mention the number of rows you are expecting in this table; that information would be instrumental in making great recommendations.
In-row data is limited to a maximum of 8060 bytes, which is the size of a single page of data, less the required overhead for each page. Any single row larger than that will result in some off-page storage of row data. I'm certain other contributors to http://dba.stackexchange.com can give you a much more concise definition of the engine internals regarding storage of large rows. How big is your largest row, presently?
If items in Data1, Data2, Data3...
have the same values occurring in a different order, the checksum will be different, so you may want to take that into consideration.
Following a brief discussion with the fantastic Mark Storey-Smith on The Heap, I'd like to offer a similar, although potentially better choice for calculating a hash on the fields in question. You could alternately use the HASHBYTES()
function in the calculated column. HASHBYTES()
has some gotchas, such as the necessity to concatenate your fields together, including some type of delimiter between the field values, in order to pass HASHBYTES()
a single value. For more information about HASHBYTES()
, Mark recommended this site. Clearly, MSDN also has some great info at http://msdn.microsoft.com/en-us/library/ms174415.aspx
Best Answer
Easy:
Or, I guess in this case it'd be
Take your pick of variations on this.
In all likelihood you may be able to test this now against your current system, but any number of small changes in the database over time could invalidate your testing. The character string could change, someone could create a lower case stored procedure that has destructive potential - anything. You can never say with 100% confidence that there isn't a destructive 26 character attack someone could construct.
I suggest you find a way to make the developer follow basic industry standard best security practices, if only for your own sake as someone who I presume is at least partially responsible should security breaches happen.
Edit:
And for maliciousness/fun, you could try enabling every trace flag. This would be interesting to observe. Feels like a blog post Brent Ozar would make...