ansaurus

Question

Sanity Check - Concatenating Date Values - SQL Injection

Answer 1

A:

if that statement is a stored procedure, you won't suffer from sql injection anyway

if that statement is generated in a language - php for example - then just make sure you escape the strings on the way in (replace ' with \' for mysql or '' for mssql) to avoid injection attacks.

oedo 2010-04-12 11:46:03

Thing is that if you pass values through and concatenate, you can be hit with injection, i'm just guessing that between the size constraints and the `convert` it should be safe.

Kyle Rozendo 2010-04-12 11:47:18

how can you be hit by injection by concatenating values? once values are inside sql it's safe, sql injection occurs when you generate a sql STATEMENT and then execute it. the only time that happens inside sql is if you call `sp_executesql` where you do need to watch for injection attacks :) the size constraints are good and the convert sanity-checks the data so you should be absolutely fine.

oedo 2010-04-12 11:49:12

It that piece of code is in PHP or similar, the only correct way to handle this is to not do it. If you look at even your own answer, if you implement it verbatim you won't be safe, just consider a string containing "... --\' ...", in your verbatim example, you would escape the ' with another \, but that would just end up as "... --\\'" which would escape the backslash, not the quote. The reason SQL injection is best avoided completely by not doing it, is that it is very hard to be better at this than the people that want to attack your site.

Lasse V. Karlsen 2010-04-12 11:50:13

@oedo, many people use dynamic SQL in stored procedures. If you properly bind parameters and call the procedure, yet just concatenate a sql string within the procedure, you are open to attack.

KM 2010-04-12 11:52:38

@KM - Thanks, thats what I was trying to say.

Kyle Rozendo 2010-04-12 11:59:03

Answer 2

+1 A:

if you put use the statement like this, within a stored procedure:

select CONVERT(datetime, @Year + '-' + @Month + '-' + @Day+ ' ' + @Time)

then you should be fine, since the target datatype datetime will only receive valid date strings.

if you use it like this in your stored procedure:

EXEC ('select CONVERT(datetime, @Year+''-''+@Month+''-''+@Day+'' ''+ @Time)')

you could have an issue, unlikely, since the input strings are limited to a short length, but who knows what some hacker will think up to fit in that tiny space.

KM 2010-04-12 11:48:07

Thanks KM, the first example you gave is exactly how I do it.

Kyle Rozendo 2010-04-12 12:00:01

ansaurus

tags:

views:

answers:

Sanity Check - Concatenating Date Values - SQL Injection

related questions