[posted and mailed, please reply in news]
> I'm creating a website that is executing a stored procedure from my
> SQL database. I can get this to work fine if I only have one input
> parameter. The problem though is that all of my stored procedures
> need several parameters inputted in order to execute (for example:
> entering the month and year).
> Since I can get this statement to work just fine if I eliminate the
> second input parameter I beleive that I must have the sytax incorrect
> for the second parameter.
> Here's what I have that DOESN'T work:
> Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
> txtMonth.value & txtYear.value)
> ChartSpace1.DataSource = DSC
> ChartSpace1.DataMember = rsd1.Name
Unless there are trailing spaces in txtMonth.Value, the values will be
concatenated into one. If there are spaces, you will indeed get a
syntax error. You must separate the parameters with commas. That is
however only your small problem:
Quote:> The 2 parameters are being inputted from txt boxes within my website.
If a user in the txtYear box enter
1999; DROP TABLE some_tbl
what will happen? Your web interface will actually pass a DROP TABLE
command to SQL Server, so you are only dependent that the account from
which the web server access SQL Server does not have permission to
drop tables. Or any other security-sensitive operations.
You should rewrite your application to not build SQL commands on the
fly, but use RPC calls or prepared queries. There is more than security
to gain from this. You win performance, and you are likely to get better
handling of bad input from the user. For instance if the user accidently
hits the space key in the middle of the year, he will get back an ugly
SQL error (unless you have some general handling of this).
Unfortunately, I cannot assist by describing how to write RPC calls or
prepared queries, as I am not a client programmer. But there are plenty-
ful of people who should be able to help you. Or simple search the
documentation for the client library you are using.
Erland Sommarskog, SQL Server MVP
Books Online (updated!) for SQL 2000 at