|
Hi
When a input sqlparameter value contain special character(eg.') the SP is not able to execute.When the same query is executed independently it's working fine.
My query is.. Select * from SearchMetadata where title like '%IF I DON''T WIN, THE WORLD WILL SUFFER FOR IT%'
My SP is
ALTER procedure [dbo].[Search_METADATA]
@SearchCrteria varchar(8000)
as
BEGIN
Declare
@Text nvarchar(4000)
Set @Text='Select top 1000 * from vw_SearchMetadata where '+@SearchCrteria
exec (@Text)
END
I have tried with all possible solution.Placed double quote whereever single quote appeared.Tried ESCAPE claue.Couldn't find soluiton
Need suggestion to solve this.Appreciate your help.
Regards
Krish
|
|
|
|
|
chovdry wrote: When a input sqlparameter value contain special character(eg.') the SP is not able to execute
That's because you are injecting into the SQL command. A very bad and dangerous practice. You should read up on SQL Injection Attacks and how to prevent them[^]
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
I am not bothered about sql injection.i want the SP to be executed. Is there a way to escape special characters.
|
|
|
|
|
You should be worried about SQL Injection - your database can be completely trashed or information could be disclosed if the user can sneak in data that you're not handling correctly. Parameters correctly handle any escaping necessary - I believe for SQL Server that they're passed 'out-of-band', not actually replaced in the text passed to the database engine.
There's not much point packing this in a stored procedure. You don't have the flexibility of variable parameter lists.
Instead you should form the query text yourself. Any time you need to insert user data into the query text, instead use a parameter name (@param) and add a corresponding parameter to the command's parameters collection.
For example:
using ( SqlConnection conn = new SqlConnection( connectString ) )
{
using ( SqlCommand cmd = new SqlCommand( conn ) )
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT top 1000 * from vw_SearchMetadata where ";
if ( title != "" )
{
cmd.CommandText += "title like @title ";
cmd.Parameters.Add( new SqlParameter( "@title", title ) );
}
using ( SqlDataReader rdr = cmd.ExecuteReader() )
{
}
}
}SQL Server will cache the query plan for a parameterised query. It has a slightly lower weight than for a stored procedure, but a higher weight than an auto-parameterized query (where SQL Server tries to figure out what's replaceable itself).
|
|
|
|
|
check this
DECLARE @SearchCrteria varchar(8000)
Declare @Text nvarchar(4000)
SET @SearchCrteria = 'title like ''%IF I DON''''T WIN, THE WORLD WILL SUFFER FOR IT%'''
--print @SearchCrteria
Set @Text='Select top 1000 * from TblA where '+@SearchCrteria
exec (@Text)
Regards
KP
|
|
|
|
|
While technically correct, it's bad practice. That's why I've voted you down.
|
|
|
|
|
Yes. you are right.
I know this is bad practice.
Regards
KP
|
|
|
|
|
Hallo all
I'm having a problem with some data that I've taken from SQL and copied into an Excel spreadsheet.
Please try this and type the following into these cells:
A1 : -982.21
B1 : 908.66
C1 : -73.55
D1 : =IF(A1+B1=C1,"Correct","Incorrect")
D1 shows "incorrect" which doesn't make sense since A1 + B1 does equal C1. This happens on a couple of rows, I've checked and they should show correct. I have also tried using the SUM function instead of adding A1+B1 and I even tried (IF C1-B1=A1) but it didn't work either. I have formatted the columns as decimal numbers.
Am I missing something really simple here?
Any clues would be appreciated
Thanks
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
Rounding errors in the calculation are probably the cause. I'd imagine that excel uses decimal numeric type because it is used so much for financial calculations, while the FLOAT type in the database is just that.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
I think you're right.
I used the SUM function on -982.21 and 908.66.
I then increased the decimal places on the answer cell to 25 and I saw this
-73.5500000000001000000000000
The previous values don't have anything after their first 2 decimal places so I don't know where that 1 is coming from. Will make a plan though.
Thanks for your help 
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
If you haven't solved it already - Try:
=IF(ROUND(A1+B1-C1,5)=0,"Correct","Incorrect")
|
|
|
|
|
Thanks, I already made SQL do the decision and that works. I tried your formula though and it works great.
Thanks
Scott
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
guys again I am throwing you the question
salary
====
1000
2000
3000
4000
4500
1500
here i want to show the max value of two records
means want to show 4500 and 4000
how to do that
plz tell me the query
i need urgently...
Magi
|
|
|
|
|
hi,
first read all the values in a vector and the use array.sort(vector).
then you can take the values.
hope this help.
|
|
|
|
|
Are you sure you can do that? I've never seen anything like that in SQL.
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
Use the TOP keyword like so:
SELECT TOP 2 salary FROM tablename
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
smyers wrote: SELECT TOP 2 salary FROM tablename
I think you need to sort it in descending order to get the result what author expected
|
|
|
|
|
Don't lie, you just made that up.
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
smyers wrote: Don't lie, you just made that up.
|
|
|
|
|
SELECT TOP 2 Salary FROM TableName ORDER BY Salary DESC
|
|
|
|
|
use
SELECT TOP 2* Salary FROM TableName ORDER BY Salary DESC
Sathiya
|
|
|
|
|
Hi,
There is sqlserver 2005 installed in my machine. but i am not able to find the northwind database in the management studio . Can anybody help me to find this database. should i install any addiotional files to get northwind database.
Thanks in advance
Sebastian
|
|
|
|
|
You need to download them from microsoft's site.
Northwind is for 2000 and whilst it works on 2005 AdventureWorks is the sample designed from 2005 and is the recommended sample.
Northwind[^]
AdventureWorks[^]
|
|
|
|
|
Thank you very much .
sebastian
|
|
|
|
|
no problem 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
