Prev: Joines Ralated
Next: SQL 2005 Query Response Time
From: moonsulu via SQLMonster.com on 17 Dec 2009 11:43
hi guys how do i do this SQL statements when a user typed in search text
field in my ASP.net webpage? How do I 'chop' the typed-in phrases?

examples:

1. typed in phrase: marketing assistant jobs

the resulting SQL statement should be:

SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, 'marketing AND assistant AND jobs')

2. typed in phrase: public administration

the resulting SQL would be:

SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, 'public AND administration')

3. typed in phrase: SQL server full text search
result would be:

SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, 'SQL AND server AND full AND text AND search')



thanks for any ideas.

From: Geoff N. Hiten on 17 Dec 2009 13:17
I suggest posting your IP address and SA credentials on your public-facing
web site. That way you can save some poor hacker the trouble of exploiting
the SQL Injection hole this leaves in your application.

Seriously, NEVER, NEVER, NEVER concatenate unconstrained user input text
into your SQL Statements.

http://xkcd.com/327/

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP


"moonsulu via SQLMonster.com" <u50488(a)uwe> wrote in message
news:a0bb23f5e94f6(a)uwe...
> hi guys how do i do this SQL statements when a user typed in search text
> field in my ASP.net webpage? How do I 'chop' the typed-in phrases?
>
> examples:
>
> 1. typed in phrase: marketing assistant jobs
>
> the resulting SQL statement should be:
>
> SELECT field1,field2
> FROM myTable
> WHERE CONTAINS(*, 'marketing AND assistant AND jobs')
>
> 2. typed in phrase: public administration
>
> the resulting SQL would be:
>
> SELECT field1,field2
> FROM myTable
> WHERE CONTAINS(*, 'public AND administration')
>
> 3. typed in phrase: SQL server full text search
> result would be:
>
> SELECT field1,field2
> FROM myTable
> WHERE CONTAINS(*, 'SQL AND server AND full AND text AND search')
>
>
>
> thanks for any ideas.
>

From: Jay on 20 Dec 2009 01:09
LMFAO! You beat me to it and you replied WAY better than I think I could
have.

"Geoff N. Hiten" <SQLCraftsman(a)gmail.com> wrote in message
news:u4ngNU0fKHA.2184(a)TK2MSFTNGP04.phx.gbl...
>I suggest posting your IP address and SA credentials on your public-facing
>web site. That way you can save some poor hacker the trouble of exploiting
>the SQL Injection hole this leaves in your application.
>
> Seriously, NEVER, NEVER, NEVER concatenate unconstrained user input text
> into your SQL Statements.
>
> http://xkcd.com/327/
>
> --
> Geoff N. Hiten
> Principal SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "moonsulu via SQLMonster.com" <u50488(a)uwe> wrote in message
> news:a0bb23f5e94f6(a)uwe...
>> hi guys how do i do this SQL statements when a user typed in search text
>> field in my ASP.net webpage? How do I 'chop' the typed-in phrases?
>>
>> examples:
>>
>> 1. typed in phrase: marketing assistant jobs
>>
>> the resulting SQL statement should be:
>>
>> SELECT field1,field2
>> FROM myTable
>> WHERE CONTAINS(*, 'marketing AND assistant AND jobs')
>>
>> 2. typed in phrase: public administration
>>
>> the resulting SQL would be:
>>
>> SELECT field1,field2
>> FROM myTable
>> WHERE CONTAINS(*, 'public AND administration')
>>
>> 3. typed in phrase: SQL server full text search
>> result would be:
>>
>> SELECT field1,field2
>> FROM myTable
>> WHERE CONTAINS(*, 'SQL AND server AND full AND text AND search')
>>
>>
>>
>> thanks for any ideas.
>>
>


From: Plamen Ratchev on 20 Dec 2009 06:18
You can split the string to words but then you have to concatenate back. Here are two articles on both operations:

http://www.sommarskog.se/arrays-in-sql-2005.html
http://www.projectdmx.com/tsql/rowconcatenate.aspx

However, there is really no need for that. You can simply do this:

SET @user_input = REPLACE(@keywords, ' ', ' AND ');

SELECT col1, col2
FROM MyTable
WHERE CONTAINS(*, @keywords);

--
Plamen Ratchev
http://www.SQLStudio.com
From: Plamen Ratchev on 20 Dec 2009 06:19
Correction:

SET @keywords = REPLACE(@keywords, ' ', ' AND ');

SELECT col1, col2
FROM MyTable
WHERE CONTAINS(*, @keywords);

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Joines Ralated
Next: SQL 2005 Query Response Time