|
Simple - don't use string concatenation to build your query.
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?
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
|
|
|
|
|
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.
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;
|
|
|
|
|
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 
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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.)
|
|
|
|
|
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."
|
|
|
|
|
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.
|
|
|
|
|
--- 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.
|
|
|
|
|
Wondering on how much these dot net data structures are used by everyone else ?
|
|
|
|
|
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 
I prefer to fetch the raw values from said reader, as I rarely need the overhead a table-class introduces.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Use them most of the time as they are easy and do all the hard work.
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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!
|
|
|
|
|
thank you .for your reply mate.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
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."
|
|
|
|
|
What you could do is stop crossposting[^]. Tips are here[^].
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Where can i get a good reference in database design (typical steps of database desining)
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
