Database

SQL Server store procedure and prevent sql injection

11-Oct-17 7:36

Hi,

I am trying to run an SSIS Package that's installed in MSDB on my Server manually, the problem is, when I run the Package in 32 bit mode, it gives me the following error:

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode

When I run the Package in 64 bit mode it gives me the following error:

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode

When I am checking the drivers that are installed I am seeing the Microsoft.ACE.OLEDB.12.0 driver there, when I try to install it, it gives me error message saying its already there I need to uninstall the existing one 32 bit, and I am seeing the Excel 14.0 drivers there too, just paused not understanding what to do, because its happening only on Server and only for Excel files, even though the SSIS uses Ace 12.0 drivers for csv and txt (comma separated, tab delimited or pipe delimited) files, they are not giving any problem (I mean the Packages that are using those files are importing fine only Packages using Excel files are giving problems), and I am importing the files successfully without any issues.

I tried to change the provider at runtime using by Changing the connection string like below: "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + @[User::FilePath] + ";Extended Properties=\"EXCEL 14.0 XML;HDR=YES\"", its giving compile error, so just paused not able to find anything I can do. In google its only showing to change the mode of the Package running 32 bit and 63 bit, I did try both modes both are giving me errors, and this Package is running fine in Development environment, I mean if I am running within SSDT, any help can be very helpful.

Can somebody suggest me anything I can do except converting the Excel files into Pipe delimited (I have already implemented that option) but its more work on the SSIS side to change all those Packages to be able to import the files into SQL Server db.

Any help would be greatly helpful, thanks in advance my friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

-- modified 11-Oct-17 15:29pm.

Mou_kol6-Oct-17 8:45

Mou_kol

6-Oct-17 8:45

i see this article How to prevent SQL Injection in Stored Procedures[^]

i compose my code like that way but not save from sql injection in my code. what is wrong there in my code ?

SQL

DECLARE @Name NVARCHAR(50)
    DECLARE @sqlcmd NVARCHAR(MAX);
    DECLARE @params NVARCHAR(MAX);
    SET @Name ='like ''%ra%''';
    SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name '+@Name;
    SET @params = N'@Name NVARCHAR(50)';
    EXECUTE sp_executesql @sqlcmd, @params, @Name;

suppose i have store proc where i will send name when i will call store proc. so we can send parameter value like 'like ''%ra%'''

sp_executesql not saving me from injection problem. guide me how to prevent injection in procedure. thanks

Re: SQL Server store procedure and prevent sql injection

Richard Deeming6-Oct-17 9:27

Richard Deeming

6-Oct-17 9:27

Simple - don't use string concatenation to build your query.

SQL

DECLARE @Name NVARCHAR(50);
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);

SET @Name = '%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name Like @Name';
SET @params = N'@Name NVARCHAR(50)';

EXECUTE sp_executesql @sqlcmd, @params, @Name;

Now the question is, why are you using dynamic SQL for such a simple query?

SQL

DECLARE @Name NVARCHAR(50);

SET @Name = '%ra%';

SELECT * FROM [TestDB].[dbo].emp1 WHERE Name Like @Name;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Server store procedure and prevent sql injection

Mou_kol6-Oct-17 11:30

Mou_kol

6-Oct-17 11:30

i just try to simulate how to prevent sql injection in sql server store proc but i saw it did not work. %ra% works. i thought sp_executesql will prevent that but did not.

so tell me what is way out
see my fresh code where %ra% is working instead of protection.

SQL

DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name like @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;

Re: SQL Server store procedure and prevent sql injection

Eddy Vluggen7-Oct-17 2:22

Re: SQL Server store procedure and prevent sql injection

7-Oct-17 2:22

Mou_kol wrote:
i thought sp_executesql will prevent that but did not.

"sp_executesql" executes sql. The SQL-command has no need to check for injection by the user, as most users will not directly access the database.

There is no way you can add strings and magically secure them.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Richard Deeming9-Oct-17 8:59

Richard Deeming

9-Oct-17 8:59

Mou_kol wrote:
see my fresh code where %ra% is working instead of protection.

What do you mean? Confused | :confused:

If you mean it's matching records based on the wildcards, then that's exactly what you've told it to do. There's no SQL Injection there.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Server store procedure and prevent sql injection

MadMyche22-Nov-17 4:13

MadMyche

22-Nov-17 4:13

The primary use of sp_ExecuteSQL is when you the Database Developer absolutely needs to create Dynamic SQL, and not for protection.

While what you have written appears safe, there is no need for Dynamic SQL and you are only adding a layer of service and reducing performance.

Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional

Importing the integer values that are coming within "

indian1435-Oct-17 14:14

Re: Importing the integer values that are coming within "

5-Oct-17 14:14

Hi,

I am getting a flat file that's getting number values like integer and decimal values also withing "" like id is also coming as "1" or "100" etc, so in ssis package in Data Conversion its failing, can I get any expression or something to eliminate those double quotes and import the value into the Database with proper conversion everything.

I am getting the error message as below:

FileFullName: \\xxxxx\\DataExchange\ShieldLink\Test\32 Bit and 64 Bit Testing\Thirty Two Bit.csv, SSISPackageName: ImportPipeDelimitedCSV.dtsx, Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, ErrorCode : -1071607767, Description : SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column1]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column1]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
, SubComponent: Data Conversion [2], HelpFiledtsmsg110.rll, HelpContext0, IdofInterfaceWithError{B6F6D221-FC27-4F71-B5A0-597583986C28}

This is failing because the values are coming as below:

Column1|Column2|Column3|
"1"|"Column 2 Value 1"|"Column 3 Value 1"

For texts its able to convert properly but for the numerical values its failing is there anything that I can do to eliminate those " and implement Data conversion without any problems thanks in advance.

But in some situations I get this error, I am not sure why is this error coming, could it lead to not load data, sometimes even with this warning also we are able to load the Data I think so, but I am not sure, the Warning is as below:

FileFullName: \\xxxxx\ShieldLink\Test\32 Bit and 64 Bit Testing\Thirty Two Bit.csv, SSISPackageName: ImportPipeDelimitedCSV.dtsx, Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, WarningCode : -2147183868, Description : Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
, SubComponent: SSIS.Pipeline, HelpFiledtsmsg110.rll, HelpContext0, IdofInterfaceWithError{B6F6D221-FC27-4F71-B5A0-597583986C28}

I am not understanding is it a Data conversion issue or some other privileges issue, any help would be greatly helpful, thanks in advance friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

-- modified 5-Oct-17 20:31pm.

jschell6-Oct-17 7:18

You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office Products installed

6-Oct-17 7:18

indian143 wrote:
is there anything that I can do to eliminate those " and implement Data conversion without any problems

Based on the following - no.

Connect to a Flat File Data Source (SQL Server Import and Export Wizard) | Microsoft Docs[^]

You might want to review the actual input spec yourself in the hope the docs are wrong.

Presuming there is no option, and you cannot get the source adjusted, then you must
1. Take all of the values as strings
2. Add a post process step to convert to correct values (remove quotes and convert to numeric as needed.)

indian1432-Oct-17 11:49

Re: You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office Products installed

2-Oct-17 11:49

Hi friends,

I am getting the following message when I am trying to install the Ace 16.0 64 bit drivers

You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office Products installed. If you want to install 64-bit MS access Engine 2016, you need remove the 32-bit installation of Office Products.

But here is the thing I am trying to install these drivers with Passive switch as below, still I am getting this error

C:\Personal Docs\DownLoads\Ace Drivers>AccessDatabaseEngine_X64.exe /passive

Just as it is mentioned here: https://knowledge.autodesk.com/support/autocad-civil-3d/learn-explore/caas/sfdcarticles/sfdcarticles/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.html

Earlier I did it with Ace 12.0 64 bit drivers, I could able to install both the 32 and 64 bit Ace 12.0 drivers side by side with Passive switch, to do the same with Ace 16.0 its not working is there any Work around to install these 64 and 32 bit drivers side by side, any link, a suggestion anything helps my friends its little urgent - thanks in advance my friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Richard MacCutchan2-Oct-17 21:53

Richard MacCutchan

2-Oct-17 21:53

Did you fudge the registry as suggested in the article? Either way, it is unlikely that Microsoft would support this, so if it does not work there is probably nothing you can do.

Re: You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office Products installed

jschell4-Oct-17 7:53

How Commonly used are the dotNet Datatables and Datasets?

4-Oct-17 7:53

--- Solution 1

Use a Virtual Machine (VM). One OS in VM 'A' would have one version and the other VM would have the other.

If you actually need both on a single machine in the delivery (production) then you could.
1. Create a service that encapsulates one service and exposes one, not both of the drivers. Call this driver 'A'
2. It doesn't do anything but allow you to run the driver.
3. Run that in a VM
4. On the regular machine (not VM) install the other driver 'B', then you app uses 'B' directly and makes rest calls to the service, via the VM, to run 'A'.

--- Solution 1

This might or might not work. And it would take a lot of experimentation. And you MUST check the license agreement to validate this usage.

1. Neither driver should be installed.
2. Install one driver, probably 32 bit one
3. Determine exactly what files were installed.
4. Copy those files somewhere.
5. Uninstall the driver.
6. Install the other driver.
7. Create a library that encapsulates the first driver in the executable itself. So not as an installed driver but rather as a library. Your library exposes the functionality you need.
8. Use your library to get to the first driver and the regular idiom to get to the second.

Note that if you can get this to work then it might best to do it with both drivers rather than relying on different access methodologies.

MarcusCole683328-Sep-17 6:06

MarcusCole6833

28-Sep-17 6:06

Wondering on how much these dot net data structures are used by everyone else ?

Re: How Commonly used are the dotNet Datatables and Datasets?

Eddy Vluggen28-Sep-17 6:58

Re: How Commonly used are the dotNet Datatables and Datasets?

28-Sep-17 6:58

Quite often, even though I personally rarely use them; you'll find them in a lot of brownfields, as it is quite easy to load a datatable using a reader. Those are also common examples in most tutorials, making it quite accessible Smile | :)

I prefer to fetch the raw values from said reader, as I rarely need the overhead a table-class introduces.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Richard MacCutchan28-Sep-17 9:45

Richard MacCutchan

28-Sep-17 9:45

Use them most of the time as they are easy and do all the hard work.

Re: How Commonly used are the dotNet Datatables and Datasets?

Mycroft Holmes28-Sep-17 14:08

Mycroft Holmes

28-Sep-17 14:08

As soon as I get the data from the database I move it into a model/object so I never work with the dataset/table object. In future I will move the datatable to a Json string on the server and let the client move it into a model/object.

Never underestimate the power of human stupidity
RAH

how to get the music database for my website?

Member 1322076227-Sep-17 7:57

Member 13220762

27-Sep-17 7:57

I am trying to make a website like SONGS STORE where people can download there favorite song of their artists.So for this I want a songs collection database.Where can i get this kind of database?

Re: how to get the music database for my website?

OriginalGriff27-Sep-17 8:00

OriginalGriff

27-Sep-17 8:00

Since most music is under copyright, your "obtaining" a database of music to download would be illegal, and we do not get involved in that at all.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!

Re: how to get the music database for my website?

Member 1322076224-Nov-17 19:08

Member 13220762

24-Nov-17 19:08

thank you .for your reply mate.

Re: how to get the music database for my website?

jschell27-Sep-17 9:28

SSIS Packages are running on Staging but not on the Production Server - little bit Urgent my friends

27-Sep-17 9:28

I would suppose any of the existing sites would allow you to relabel to your brand for enough money.

Otherwise you would need to do it like they did, go to the artists/companies and get their permission (contract agreement) to allow you to host their music.

indian14326-Sep-17 6:34

Re: SSIS Packages are running on Staging but not on the Production Server

26-Sep-17 6:34

Hi,

I have some SSIS Packages that are getting executed using a .Net Application, when I execute them in Staging Environment they are properly executing and importing the Data, but when I execute them on Production Server they are not importing the Data, I am unable to debug because its a Production Server, I have enabled Logging on the Packages to write into a Table, still the error message is not much useful any help would be greatly helpful. Thanks in advance. The error message I am getting into the ErrorLog Table is:

Execution failed for SSIS Package: BillingDataLoad, Error description: FileFullName: Source Is Db, SSISPackageName: BillingDataLoad.dtsx, Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, InformationCode : 1074041076, Description : Lookup has cached 16 rows. , SubComponent: Lookup [82], HelpFiledtsmsg110.rll, HelpContext0, IdofInterfaceWithError{B6F6D221-FC27-4F71-B5A0-597583986C28}

When I implemented SSIS Logging in my Application, I found that both 32 bit Jet 4.0 and 64 bit Ace 12.0 drivers are not registered on Production Server. But there are 32 bit and 64 bit Ace 14.0 drivers are there, then why Package is not able to execute on Production? Are Ace 14.0 64 bit and Ace 12.0 64 bit drivers not compatible to each other?

Another thing is, even though I have Ace 14.0 drivers on my local machine Data tools 2012 is taking Ace 12.0 64 bit drivers when I Create Data flow task using an xlsx file as data source? Why is it not taking Ace 14.0 drivers instead, I am creating the packages with Visual studio 2012 and .Net 4.0. Why is my Package taking Ace 12.0 drivers by default and expecting it to be registered on the Server.

Is there any solution for me? Can anybody please suggest me, so any change I have to make should have solid ground because I am going to make that change on the Production server. Please help I need some guidance or a link anything. By the way I tried both the .xls and .xlsx files and run Package under both 32 bit and 64 bit modes in both cases its failing, because 32 bit failing for Jet drivers not registered and 64 bit is failing by saying Ace 12.0 not registered when Ace 14.0 is registered there.

Description  {0}Excel Source failed validation and returned error code 0xC020801C.
, HelpContext  {0}0, HelpFile     {0}dtsmsg110.rll, IDOfInterfaceWithError {0}{B6F6D221-FC27-4F71-B5A0-597583986C28}, Source       {0}
Data Flow Task, Subcomponent {0}SSIS.Pipeline, Timestamp    {0}9/27/2017 5:03:34 PM, ErrorCode    {0}-1073450985
Description  {0}SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  
The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209302.  
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
, HelpContext  {0}0, HelpFile     {0}dtsmsg110.rll, IDOfInterfaceWithError {0}{B6F6D221-FC27-4F71-B5A0-597583986C28}, Source       {0}
Data Flow Task, Subcomponent {0}Excel Source [2], Timestamp    {0}9/27/2017 5:03:34 PM, ErrorCode    {0}-1071611876
Description  {0}The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, 
see http://go.microsoft.com/fwlink/?LinkId=219816
, HelpContext  {0}0, HelpFile     {0}, IDOfInterfaceWithError {0}{AC9502ED-5A99-4CB0-A467-B2AB5A157E70}, Source       {0}
ImportXlsxFiles, Subcomponent {0}Connection manager "Excel Connection Manager", Timestamp    {0}9/27/2017 5:03:34 PM, ErrorCode    {0}-1073676245
Description  {0}The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. 
If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  
Description: "Class not registered".
, HelpContext  {0}0, HelpFile     {0}, IDOfInterfaceWithError {0}{AC9502ED-5A99-4CB0-A467-B2AB5A157E70}, 
Source       {0}ImportXlsxFiles, Subcomponent {0}Connection manager "Excel Connection Manager", 
Timestamp    {0}9/27/2017 5:03:34 PM, ErrorCode    {0}-1071607038
Package errors count after running the package:   {0}6

And another thing I found is, when I saw the Server it is showing the Ace 12.0 drivers in the drivers list, is it corrupted or over written or I need to re register it, etc are my questions.

The above is the error message I am getting, I am not getting any error messages for the .csv and .text files only for the excel files. So is there any way I can resolve this issue.

And another thing I found is, when I saw the Server it is showing the Ace 12.0 drivers in the drivers list, is it corrupted or over written or I need to re register it, etc are my questions. If I can resolve this issue without much change in the Server like re-registering the Ace 12.0 Drivers etc it would be nice to do that, since those drivers are showing up in the Drivers list.
Another thing I did was I installed SSDT 2017 to check if that takes Ace 14.0 drivers by default because I thought the new Versions of Office might have over written the Ace 12.0 drivers but no SSIS still is looking only Ace 12.0 drivers for 64 bit Excel files? I am not sure is it because of the SQL Server Version or SSIS Engine Version or Visual Studio Version or do SSDT always takes only Ace 12.0 drivers for Excel I am not sure, it would be a great help for me if somebody can help me with some guidance or any link etc, any thing helps my friends. I am also searching and applying different options, if somebody has gone through the same and resolved it, it would be a great help.

I am not trying to re-post or cross post this in hope of somebody might have gone through the same and can give me the valuable suggestions - Thanks in advance my friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

-- modified 2-Oct-17 13:41pm.

Eddy Vluggen27-Sep-17 3:35

Re: SSIS Packages are running on Staging but not on the Production Server

27-Sep-17 3:35

So, after the crossposting, we try reposting. Make an image of your production server, load the image in a VM, and debug.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

jschell27-Sep-17 9:31

Need to find out what drivers are missing on the Production Server

27-Sep-17 9:31

Following certainly suggests that more information is available.

DtsError.IDOfInterfaceWithError Property (Microsoft.SqlServer.Dts.Runtime)[^]

indian14321-Sep-17 7:32

Re: Need to find out what drivers are missing on the Production Server

21-Sep-17 7:32

Hi all,

I have a .Net application which is executing SSIS packages, when I run that application on the Staging Server, it runs fine, but when I run the same App on Production server it doesn't import the Data into Tables from Excel files.

When I caught the errors and warnings I get only this Warning, which doesn't help me a lot. Anybody if can suggest me something what to do? it would be great help. I doubt that there maybe drivers missing on the Production Server but until I knew perfectly I can't install anything on Production Server, any advice or a link or even code anything helps me, thanks in advance friends.

FileFullName: \\xxxxxxxxxxxxxxx\xxxxxx\Prod\MemberMaint092017.xlsx, SSISPackageName: SgLgReporting_SLMemberMaint_New.dtsx, 
Source : Microsoft.SqlServer.Dts.Runtime.Package, WarningCode : -2147381246, Description : SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  
The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); 
resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. 
Change the MaximumErrorCount or fix the errors.
, SubComponent: , HelpFile, HelpContext-2147381246, IdofInterfaceWithError{9744DAE0-F87A-45FC-B444-89E65C74BBF6}

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Eddy Vluggen21-Sep-17 23:23