Dynamic database name

Question

Post reply

Dynamic database name

tienvo

SSC Enthusiast

Points: 132
More actions
August 8, 2006 at 7:12 pm

#113845

Hi Guys,
I've got this issue and need you guys help.
i want to be able to pass in the database name to a select statement,
for example:
declare @dbname varchar(10)
set @dbname = 'dev'
then
select * from @dbname.dbo.tblAccounts
obviously this will result in an error,

Viewing 15 posts - 1 through 15 (of 22 total)

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

growl Hall of Fame Points: 3068 More actions · Answer 1

Try this

declare @dbname varchar(10)

declare @sqlstr as nvarchar(500)

set @dbname = 'master'

set @sqlstr = 'select * from ' + @dbname + '.dbo.sysfiles'

exec sp_executesql @sqlstr

Hope this helps

Andrew Clarke-161005 Mr or Mrs. 500 Points: 587 More actions · Answer 2

Agreed. A minor point: In this case the slightly simpler...

EXECUTE('select * from ' + @dbname + '.dbo.sysfiles')

...might be as good

David Burrows SSC Guru Points: 65142 More actions · Answer 3

EXECUTE('select * from [' + @dbname + '].dbo.sysfiles')

to stop sql injection attack

Far away is close at hand in the images of elsewhere.
Anon.

tienvo SSC Enthusiast Points: 132 More actions · Answer 4

tienvo

SSC Enthusiast

Points: 132

August 9, 2006 at 9:40 pm

#654204

thanks guys,

tienvo

Mark Green SSCrazy Points: 2085 More actions · Answer 5

What is an sql injection attack. And how would the changes above stop that?

Andrew Clarke-161005 Mr or Mrs. 500 Points: 587 More actions · Answer 6

If your database is supporting a public-facing service such as a website, it is likely to be vulnerable to malicious or mischievous attack. The attacker 'injects' SQL code into a string or other value that is taken as an input. If the input is inadequately checked before being executed. The second SQL statement gets executed. If it is something like 'Delete from customer', then it can have unpleasant consequences. At the worst, one can get control of the database via a SQL injection.

There are several precautions the DBA can take besides cursing at the Website developers. The one that David Burrows suggests is a good one in the circumstances and I wish I'd put it in. Basically, one must always think very defensively when executing dynamic SQL. It is a good mindset to get into. I would always insist on a defined interface between the Website and the database, using only stored procedures and allowing acess only to these stored procedures, so that any attempt by a web user to access any other object such as a table is refused by the database security. All parameters passed to these stored procedures should be logged and checked within the sp for things that shouldn't be in them..

Mark Green SSCrazy Points: 2085 More actions · Answer 7

I only allow the front end user interface to submit flat files that are read by Perl programs, and SPROCS are then executed via these Perl programs. It enables me to restrict the SPROCS that can be activated.

Andrew Clarke-161005 Mr or Mrs. 500 Points: 587 More actions · Answer 8

That sounds fine as long as the 'user', which the perl process connects to the database with, is assigned rights only to those SPs. That means that the Perl process cannot accidentally execute a value as SQL which then accesses something it shouldn't. Actually, it is probably OK anyway but it pays to be 'belt and braces' nowadays. I speak from bitter experience!

David Burrows SSC Guru Points: 65142 More actions · Answer 9

Nicely put

Far away is close at hand in the images of elsewhere.
Anon.

Mike Levan SSChampion Points: 14201 More actions · Answer 10

EXECUTE('select * from [' + @dbname + '].dbo.sysfiles')

for the same query how can i add where caluse like..

EXECUTE('select * from [' + @dbname + '].dbo.sysfiles') where date='09/04/1998'

David Burrows SSC Guru Points: 65142 More actions · Answer 11

Do you mean like this (for your original query)

EXECUTE('select * from [' + @dbname + '].dbo.tblAccounts where [date]=''09/04/1998''')

Note the number of single quotes

Far away is close at hand in the images of elsewhere.
Anon.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

David Burrows (8/9/2006)

[Code]

SET @dbname = 'sysobjects] SELECT ''Hello, you are hijacked'' sp_password @old = NULL, @new ='whatever', @loginame = 'sa' --'

[/Code]

How simple is that?

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

A picture is worth 1000 words....

SQL Injection[/url]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

David Burrows SSC Guru Points: 65142 More actions · Answer 14

Good spot Sergiy 🙂

Maybe this instead then

SET @sql = 'select * from [' + REPLACE(REPLACE(@dbname,'[',''),']','') + '].dbo.tblAccounts where [date]=''09/04/1998'''

EXECUTE(@sql)

btw your sql would not work anyway, it is malformed but the premise is sound 😉

Far away is close at hand in the images of elsewhere.
Anon.