Menu
One aspect of our system requires our SQL Server instance to talk to a MySQL Server via a linked Server Connection.
MSSQL -> LinkedServer(MSDASQL ODBC Provider) -> MySQL ODBC Connector -> MySQL DB
The Table on the linked server is called in a SPROC along the lines of
CREATE PROCEDURE DoStuff
AS
SELECT a.ID, a.Name, b.OtherProperty
FROM MyDatabase..MyTable a
JOIN LINKSRVR...OtherTable b
ON a.ID = b.ID
GO
The problem is that the MySQL database lives on another network, only accessible by VPN & that the CREATE or ALTER statement breaks with the following error unless the VPN is active.
The OLE DB provider "MSDASQL" for linked server "LINKSRVR" reported an error.
The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider
"MSDASQL" for linked server "LINKSRVR".
Is there anyway to force SQL Server to just go ahead and create the SPROC with the SQL I tell it and not to try and validate if the LinkedServer is up/down.
456
Stored procedures only directly prevent SQL injection if you call them in a paramerized way. If you still have a string in your app with the procedure name and concatenate parameters from user input to that string in your code you'll have still have trouble.
In general, to prevent SQL injection, we should use parameterized queries in C# code or the stored procedures. Since we focus on the stored procedures, let's look into them. An unsafe way of writing stored procedure to execute SQL dynamic queries: Create Procedure GetData(@input varchar(350))
Explanation. Modifying or ALTERing a stored procedure is pretty simple. Once a stored procedure has been created it is stored within one of the system tables in the database that is was created in. When you modify a stored procedure the entry that was originally made in the system table is replaced by this new code.
Yes, things haven't changed much in this area, you should be using quotename for any SQL server object names that are used in dynamic SQL (especially if they are supplied externally to your code). As well as SQL injection mitigation this also means your code will work correctly for non standard identifier names.
You'd have to "hide" the offending SQL from the compiler by placing it in a string and executing it as dynamic SQL.
146
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
