Google
 

Monday, October 29, 2007

SQL Testing Options with SQL Server 2000 and 2005

Testing database applications for many organizations is a challenging task that is difficult to setup, automate and validate. Luckily, more time is getting allocated into development projects to conduct more rigorous testing. In this tip we will outline some of the realities to perform SQL Server 2000 and 2005 testing and opportunities to streamline the process.

Solution
SQL Server 2000 and 2005 offer a number of opportunities to perform application testing to include the following items:

People, Process and Technology

The reality is that people, processes and technology are needed to conduct proper testing. Taking any one item out of the equation will not result in the expected outcome. So be sure to impress upon your team that it is necessary to have an equal amount all of three components for success. It is difficult for a great piece of technology to solve a difficult problem without highly talented people and a comprehensive process.

SQL Server Instances

Both SQL Server 2000 and 2005 support a single default instance and 15 named instances on the same server. What this means is that with per CPU licensing up to 16 instances can exist on a single server without incurring any additional licensing costs. If your testing is encapsulated at a SQL Server instance level, then up to 16 versions of the code can exist on 1 server.

With SQL Server 2005, additional instance options are available for other portions of the application, i.e. Analysis Services.

SQL Server 2005 Express Edition

Another option for testing is leveraging the SQL Server 2005 Express Edition which is a free copy of the database engine and Reporting Services with limitations on the number of CPUs, memory usage, etc. This version offers a fully featured GUI and all of the bells and whistles. This means that this edition gives you the opportunity to perform functional testing of the application on multiple independent machines at a very reasonable cost.

Virtual Machines

If the testing incorporates SQL Server and other components installed in Windows, then multiple SQL Server instances may be helpful, but they are not the solution because DLL's may need to be redeployed for each test or build, limiting the capabilities for parallel testing. Leveraging the Express edition may be an option if many machines are available, but this too can quickly become unwieldy. One recommendation is to leverage Microsoft Virtual Server or VMWare as a means to have multiple virtual instances of Windows with SQL Server, IIS, your application, etc. all residing on 1 physical server. This results in the opportunity to have many independent and fully featured instances of Windows and with multiple code bases.

Synonyms

If you are familiar with other database platforms, then synonyms should be no stranger. With SQL Server 2005, Microsoft introduced synonyms as a means to have a single name of an object reference a different underlying object in another schema or database or even on another SQL Server.

*** NOTE *** - Synonyms are not available in SQL Server 2000.

Views

Since synonyms are not available in SQL Server 2000, one potentially viable option is to leverage VIEWS as a means to have a single name that can reference another object.

DDL Triggers

With many scripts underlying objects need to be created, altered or dropped. Depending on how the code is written and executed, it may be difficult to determine if these commands were successful or not. One way to fill this gap in SQL Server 2005, is by leveraging the new DDL triggers. These can be created to notify the team as the code completes and the objects are created, altered or dropped.

Friday, October 26, 2007

SQL Code Deployment Best Practices

Problem
I am the only SQL Server DBA at my organization and I feel like I am constantly pushing out code. I have Developers send me emails, I get help desk tickets, I have to go out to file shares and VSS to check for code that needs to get pushed out. I have been at a point where I am not able to get my other work done which is becoming frustrating. The other issue that I have is that I am unable to track the changes over time. With 4 different places that I need to check for code, this has become a nightmare that I have to get in control and soon. How should I go about doing this?

Solution
With the numerous code deployments on a daily or weekly basis there is not doubt that you are having an issue getting other work done. Deploying code can become a full time job if you let it become one. You are probably also facing a significant amount of fire fighting because you do not have a process in place and everything is ad-hoc.

Although there is no single answer to resolve this issue, in this tip we will outline options that can be leveraged to help you resolve your code deployment issues. These options include:

  • Schedule - First and foremost, you have to setup a schedule for the following items:
    • Code submittals
    • Code reviews
    • Code deployments
  • Managerial Support - The next big area that you need to tackle is managerial support. Take some time to record the amount of time you spend on a daily or weekly basis deploying code and how you plan to change the amount of time for code deployments and the initiatives you can address with your fee time. The key here is to think about benefits from your perspective and how you can benefit the organization. Another key area is how your change is going to impact the remainder of the organization. Most likely you will be brining order to chaos, so keep that in mind and be prepared for push back.
  • Communication - Have your management communicate the deployment plan to the development teams. You will probably get some push back when moving from an entirely ad-hoc schedule to a formalized schedule, but you need to stick to your schedule or negotiate a reasonable plan that works for the entire team. In a nutshell you do not want to stop progress, but do not want to constantly be facing chaos and firefighting.
  • Technology - To streamline the process consider a third party tool to package and deploy the code. Some of these tools may be
  • Just say "no" to the drive by requests - If your team has been accustomed to being able to send a request and have you as the DBA fulfill it, then any change to that process is going to cause some friction. Be aware that at times code does need to get pushed out if it is an emergency. Those needs should be identified, but you should have sign-off from management above the Development and DBA managers for this type of approval. If not, every request will become an emergency and the process you are trying to put in place will not get off the ground.
  • Coding Needs - Most likely if you do not have a standardized code deployment process now then items like comments, deployment code, rollback code, etc are non-existent. Moving forward this is something you should need in order to have a smooth code deployment perspective.
    • Check out this coding comments example.
    • Depending on what is being deployed should dictate the deployment scripts or steps. If you can code it then it can be retained for historical purposes and reviewed if an issue arises in the future.
    • Rollback scripts should be the reciprocal of the code or data being deployed. Having these scripts could be the difference between having an extended downtime or just a blimp on the radar screen. Typically these scripts never see any action, but when they are needed, they are a life saver.
  • Sign-Off - Depending on the formality in your organization dictates the level of formality with sign-off. At some organizations a verbal sign-off is all that is needed, in other organizations it is exchanging emails until you reach agreement and in the third category of organizations a formal written sign-off is necessary. See what makes sense and do not make this process so rigid that no one wants to participate.
  • Process - When you build a schedule, obtain managerial support then communicate the schedule and follow it - what you are really doing is putting a process in place. Do not be afraid of it or resist it, just be aware that it is something you are trying to put into place to better manage your time and stop some of the chaos in your SQL Server environment.

Example Code Deployment Scenario

Below outlines a sample deployment schedule that could be considered a viable option in your SQL Server environment:

  • Code Submission - Every week on Monday @ 12:00 PM all code and rollback scripts need to be submitted to the DBA
  • Code Review - Monday afternoon the DBA reviews the code then tests it in a test environment and executes a best practices checklist on the submitted code
  • Team Meeting - Tuesday @ 9:00 AM questionable code is discussed and determined if it is included in the deployment
    • If the DBA does not have any questions then this meeting can be cancelled, but the DBA should email the team to let them know the needs
  • Deployment - Every Wednesday @ 6:00 AM have jobs that push out the code
  • Review - Every Wednesday @ 6:00 AM the DBA reviews the output and verifies the code
  • Rollback - Every Wednesday @ 6:00 AM the DBA determines if the code needs to be rolled back or not
  • Report - Every Wednesday @ 6:00 AM the DBA reports the status of the code deployment
  • History - Every Wednesday @ 6:00 AM the DBA records the changes that were successfully pushed out

Tuesday, October 23, 2007

SQL Tips : Clearing Cache for Valid Performance Testing

Problem
When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem. To determine the best overall solution, each option is tested and the results are recorded. As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves. Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison. How can I ensure during each execution of a new set of code that the data is not cached?

Solution
If all of the performance testing is conducted in SQL Server the best approach may be to issue a CHECKPOINT and then issue the DBCC DROPCLEANBUFFERS command. Although the CHECKPOINT process is an automatic internal system process in SQL Server and occurs on a regular basis, it is important to issue this command to write all of the dirty pages for the current database to disk and clean the buffers. Then the DBCC DROPCLEANBUFFERS command can be executed to remove all buffers from the buffer pool. Here is a quick code snippet to serve as an example:

USE ;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Although the CHECKPOINT and DBCC DROPCLEANBUFFERS commands seem to be the most elegant approach because they can be included in your T-SQL test scripts, you also can achieve the same results by either restarting the SQL Server instance or restarting Windows. If you are testing via a batch file (or similar) then you could issue 'net stop mssqlserver' and 'net start mssqlserver' DOS commands. As a side note, you also have the option to shutdown SQL Server via the T-SQL SHUTDOWN command, but would need to restart the services via either the 'net start' command or via one of the GUI tools. Although these options are possible, they are not recommended. These last set of commands will shut down your SQL Server instance or machine, which is probably unneeded.

A few words of caution...

It is not recommended to issue the CHECKPOINT\DBCC DROPCLEANBUFFERS, the 'net stop mssqlserver', T-SQL SHUTDOWN command or restarting Windows on production systems just for the sake of testing. These commands could have detrimental results to your environment. It is recommended to only issue these types of commands in testing environments with coordination among your team due to the impact to the overall SQL Server. In addition, keep in mind that if you do issue these commands only in test environments that if multiple tests are being conducted simultaneously issuing the CHECKPOINT and DBCC DROPCLEANBUFFERS commands may skew results for other testers.

Next Steps

  • As you conduct performance testing in the future consider including the CHECKPOINT and DBCC DROPCLEANBUFFERS command in each of your scripts to ensure cached data is not benefiting later executions of your code.
  • As you test, it may be a good idea to capture the query execution results for cold and warm cache.
  • Although, time needed for a query is important, it is also a good idea to review the query plans for the code to determine the best cost among the options.
  • For related information check out the following:


Monday, October 8, 2007

SQL : Using Views to Simplify Data Access

Problem
One challenge that just about everyone is faced with is ever changing database schemas. From the onset of a project the database schema might be perfect on day one, but as the application evolves and the business needs change database table structures have to change. In addition, as database become more and more complex there is often the challenge of having to join several tables together on an ongoing basis which is time consuming and also creates the possibility of mistakes. So what other options are there to ensure your applications do not break when your database schema changes and what is an easier way to handle multi-join queries that are used over and over again?

Solution
The simple solution here is to use Views. Views allow you to predefine what query results will look like, enable you to pre-join your tables as well as allow you to mask any sensitive data that you don't want people to access. Basically a view is a defined and saved query that can be used over and over again.

So in the AdventureWorks database there are several tables that allow us to get employee information. In order to get the following query elements:
EmployeeID
Title
FirstName
MiddleName
LastName
Suffix
Job Title
Phone
EmailAddress
EmailPromotion
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName
AdditionalContactInfo

we need to join the following tables.
HumanResources.Employee
Person.Contact
HumanResources.EmployeeAddress
Person.Address
Person.StateProvince
Person.CountryRegion

Writing this query over and over again becomes time consuming and also there are potential issues that the query will not be constructed the same way each time.

Here is what this query would look like. It is not that complex to write, but it would be easier to save this once and reuse the view each time instead of writing this query every time you needed this data.SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];


To create the view for the above query the syntax is as simple as the below code.CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];


The only difference between the first set of code and the second set of code is:

CREATE VIEW [HumanResources].[vEmployee] AS

This statement is telling SQL Server to create a view called HumanResouces.vEmployee as the query that follows. Once this view has been created instead of writing this query every time we can now use the view as follows or any query as long as the columns exist in the query.SELECT * FROM HumanResources.vEmployee


This is much easier than having to rewrite the query every time. In addition, we are now guaranteed that the result set and the joins will always be consistent.

Another advantage of using views is that the underlying tables can change and the view will still work, granted you did not drop columns that the view was using. But even if you did drop a column in a table you could change the view to return a static value and your application would continue to work without having to make any changes to the application. Although, this depends on the data and the application.

As mentioned above you can grant access to the view and not the table and therefore you can mask certain columns that have sensitive data that you need to store, but do not want everyone to access.

Views can also be joined to other tables or views. But be careful with this, because things can get quite complex to track down issues if you have a bunch of views and start joining views to views.

In addition, you can update views directly and also create indexed views for better performance. There are some constraints as to what you can update in a view, but this can be done.

Next Steps
Take advantage of all the features that SQL Server offers. Views are nothing new, but can simplify tasks that are repeated over and over again.
Look for areas in your database where views can be useful
Enterprise Manager and Management Studio provide query builds that you can use to create these queries and then save them as views.

The top 10 reasons Web sites get hacked

The top 10 reasons Web sites get hacked
Experts say the people who actually build Web applications aren't paying much attention to security; a non-profit group is trying to solve that

By Jon Brodkin, Network World


Web security is at the top of customers' minds after many well-publicized personal data breaches, but the people who actually build Web applications aren't paying much attention to security, experts say.

"They're totally ignoring it," says IT consultant Joel Snyder. "When you go to your Web site design team, what you're looking for is people who are creative and able to build these interesting Web sites... That's No. 1, and No. 9 on the list would be that it's a secure Web site."

The biggest problem is designers aren't building walls within Web applications to partition and validate data moving between parts of the system, he says.

Security is usually something that's considered after a site is built rather than before it is designed, agrees Khalid Kark, senior analyst at Forrester.

"I'd say the majority of Web sites are hackable," Kark says. "The crux of the problem is security isn't thought of at the time of creating the application."

That's a big problem, and it's one the nonprofit Open Web Application Security Project (OWASP) is trying to solve. An OWASP report called "The Ten Most Critical Web Application Security Vulnerabilities" was issued this year to raise awareness about the biggest security challenges facing Web developers.

The first version of the list was released in 2004, but OWASP Chairman Jeff Williams says Web security has barely improved. New technologies such as AJAX and Rich Internet Applications that make Web sites look better also create more attack surfaces, he says. Convincing businesses their Web sites are insecure is no easy task, though.

"It's frustrating to me, because these flaws are so easy to find and so easy to exploit," says Williams, who is also CEO and co-founder of Aspect Security. "It's like missing a wall on a house."

Here is a summary of OWASP's top 10 Web vulnerabilities, including a description of each problem, real-world examples and how to fix the flaws.

1. Cross site scripting (XSS)

The problem: The "most prevalent and pernicious" Web application security vulnerability, XSS flaws happen when an application sends user data to a Web browser without first validating or encoding the content. This lets hackers execute malicious scripts in a browser, letting them hijack user sessions, deface Web sites, insert hostile content and conduct phishing and malware attacks.

Attacks are usually executed with JavaScript, letting hackers manipulate any aspect of a page. In a worst-case scenario, a hacker could steal information and impersonate a user on a bank's Web site, according to Snyder.

Real-world example: PayPal was targeted last year when attackers redirected PayPal visitors to a page warning users their accounts had been compromised. Victims were redirected to a phishing site and prompted to enter PayPal login information, Social Security numbers and credit card details. PayPal said it closed the vulnerability in June 2006.

How to protect users: Use a whitelist to validate all incoming data, which rejects any data that's not specified on the whitelist as being good. This approach is the opposite of blacklisting, which rejects only inputs known to be bad.

Additionally, use appropriate encoding of all output data. "Validation allows the detection of attacks, and encoding prevents any successful script injection from running in the browser," OWASP says.

2. Injection flaws

The problem: When user-supplied data is sent to interpreters as part of a command or query, hackers trick the interpreter -- which interprets text-based commands -- into executing unintended commands. "Injection flaws allow attackers to create, read, update, or delete any arbitrary data available to the application," OWASP writes. "In the worst-case scenario, these flaws allow an attacker to completely compromise the application and the underlying systems, even bypassing deeply nested firewalled environments."

Real-world example: Russian hackers broke into a Rhode Island government Web site to steal credit card data in January 2006. Hackers claimed the SQL injection attack stole 53,000 credit card numbers, while the hosting service provider claims it was only 4,113.

How to protect users: Avoid using interpreters if possible. "If you must invoke an interpreter, the key method to avoid injections is the use of safe APIs, such as strongly typed parameterized queries and object relational mapping libraries," OWASP writes.

3. Malicious file execution

The problem: Hackers can perform remote code execution, remote installation of rootkits, or completely compromise a system. Any type of Web application is vulnerable if it accepts filenames or files from users. The vulnerability may be most common with PHP, a widely used scripting language for Web development.

Real-world example: A teenage programmer discovered in 2002 that Guess.com was vulnerable to attacks that could steal more than 200,000 customer records from the Guess database, including names, credit card numbers and expiration dates. Guess agreed to upgrade its information security the next year after being investigated by the Federal Trade Commission.

How to protect users: Don't use input supplied by users in any filename for server-based resources, such as images and script inclusions. Set firewall rules to prevent new connections to external Web sites and internal systems.

4. Insecure direct object reference

The problem: Attackers manipulate direct object references to gain unauthorized access to other objects. It happens when URLs or form parameters contain references to objects such as files, directories, database records or keys.

Banking Web sites commonly use a customer account number as the primary key, and may expose account numbers in the Web interface.

"References to database keys are frequently exposed," OWASP writes. "An attacker can attack these parameters simply by guessing or searching for another valid key. Often, these are sequential in nature."

Real-world example: An Australian Taxation Office site was hacked in 2000 by a user who changed a tax ID present in a URL to access details on 17,000 companies. The hacker e-mailed the 17,000 businesses to notify them of the security breach.

How to protect users: Use an index, indirect reference map or another indirect method to avoid exposure of direct object references. If you can't avoid direct references, authorize Web site visitors before using them.

5. Cross site request forgery

The problem: "Simple and devastating," this attack takes control of victim's browser when it is logged onto a Web site, and sends malicious requests to the Web application. Web sites are extremely vulnerable, partly because they tend to authorize requests based on session cookies or "remember me" functionality. Banks are potential targets.

"Ninety-nine percent of the applications on the Internet are susceptible to cross site request forgery," Williams says. "Has there been an actual exploit where someone's lost money? Probably the banks don't even know. To the bank, all it looks like is a legitimate transaction from a logged-in user."

Real-world example: A hacker known as Samy gained more than a million "friends" on MySpace.com with a worm in late 2005, automatically including the message "Samy is my hero" in thousands of MySpace pages. The attack itself may not have been that harmful, but it was said to demonstrate the power of combining cross site scripting with cross site request forgery. Another example that came to light one year ago exposed a Google vulnerability allowing outside sites to change a Google user's language preferences.

How to protect users: Don't rely on credentials or tokens automatically submitted by browsers. "The only solution is to use a custom token that the browser will not 'remember,'" OWASP writes.

6. Information leakage and improper error handling

The problem: Error messages that applications generate and display to users are useful to hackers when they violate privacy or unintentionally leak information about the program's configuration and internal workings.

"Web applications will often leak information about their internal state through detailed or debug error messages. Often, this information can be leveraged to launch or even automate more powerful attacks," OWASP says.

Real-world example: Information leakage goes well beyond error handling, applying also to breaches occurring when confidential data is left in plain sight. The ChoicePoint debacle in early 2005 thus falls somewhere in this category. The records of 163,000 consumers were compromised after criminals pretending to be legitimate ChoicePoint customers sought details about individuals listed in the company's database of personal information. ChoicePoint subsequently limited its sales of information products containing sensitive data.

How to protect users: Use a testing tool such as OWASP'S WebScarab Project to see what errors your application generates. "Applications that have not been tested in this way will almost certainly generate unexpected error output," OWASP writes.

Another tip: disable or limit detailed error handling, and don't display debug information to users.

7. Broken authentication and session management

The problem: User and administrative accounts can be hijacked when applications fail to protect credentials and session tokens from beginning to end. Watch out for privacy violations and the undermining of authorization and accountability controls.

"Flaws in the main authentication mechanism are not uncommon, but weaknesses are more often introduced through ancillary authentication functions such as logout, password management, timeouts, remember me, secret question and account update," OWASP writes.

Real-world example: Microsoft had to eliminate a vulnerability in Hotmail that could have let malicious JavaScript programmers steal user passwords in 2002. Revealed by a networking products reseller, the flaw was vulnerable to e-mails containing Trojans that altered the Hotmail user interface, forcing users to repeatedly reenter their passwords and unwittingly send them to hackers.

How to protect users: Communication and credential storage has to be secure. The SSL protocol for transmitting private documents should be the only option for authenticated parts of the application, and credentials should be stored in hashed or encrypted form.

Another tip: get rid of custom cookies used for authentication or session management.

8. Insecure cryptographic storage

The problem: Many Web developers fail to encrypt sensitive data in storage, even though cryptography is a key part of most Web applications. Even when encryption is present, it's often poorly designed, using inappropriate ciphers.

"These flaws can lead to disclosure of sensitive data and compliance violations," OWASP writes.

Real-world example: The TJX data breach that exposed 45.7 million credit and debit card numbers. A Canadian government investigation faulted TJX for failing to upgrade its data encryption system before it was targeted by electronic eavesdropping starting in July 2005.

Furthermore, generate keys offline, and never transmit private keys over insecure channels.

It's pretty common to store credit card numbers these days, but with a Payment Card Industry Data Security Standard https://www.pcisecuritystandards.org/ compliance deadline coming next year, OWASP says it's easier to stop storing the numbers altogether.

9. Insecure communications

The problem: Similar to No. 8, this is a failure to encrypt network traffic when it's necessary to protect sensitive communications. Attackers can access unprotected conversations, including transmissions of credentials and sensitive information. For this reason, PCI standards require encryption of credit card information transmitted over the Internet.

Real-world example: TJX again. Investigators believe hackers used a telescope-shaped antenna and laptop computer to steal data exchanged wirelessly between portable price-checking devices, cash registers and store computers, the Wall Street Journal reported.

"The $17.4-billion retailer's wireless network had less security than many people have on their home networks," the Journal wrote. TJX was using the WEP encoding system, rather than the more robust WPA.

How to protect users: Use SSL on any authenticated connection or during the transmission of sensitive data, such as user credentials, credit card details, health records and other private information. SSL or a similar encryption protocol should also be applied to client, partner, staff and administrative access to online systems. Use transport layer security or protocol level encryption to protect communications between parts of your infrastructure, such as Web servers and database systems.

10. Failure to restrict URL access

The problem: Some Web pages are supposed to be restricted to a small subset of privileged users, such as administrators. Yet often there's no real protection of these pages, and hackers can find the URLs by making educated guesses. Say a URL refers to an ID number such as "123456." A hacker might say 'I wonder what's in 123457?' Williams says.

The attacks targeting this vulnerability are called forced browsing, "which encompasses guessing links and brute force techniques to find unprotected pages," OWASP says.

Real-world example: A hole on the Macworld Conference & Expo Web site this year let users get "Platinum" passes worth nearly $1,700 and special access to a Steve Jobs keynote speech, all for free. The flaw was code that evaluated privileges on the client but not on the server, letting people grab free passes via JavaScript on the browser, rather than the server.

How to protect users: Don't assume users will be unaware of hidden URLs. All URLs and business functions should be protected by an effective access control mechanism that verifies the user's role and privileges. "Make sure this is done ... every step of the way, not just once towards the beginning of any multistep process,' OWASP advises.

Full Text Search Querying Alternatives

Problem
In one of your recent tips, you outlined setting up a SQL Server 2005 Full Text Catalog and some basic queries. Can you outline some of the Full Text Search querying alternatives? I thought your first tip (Making the case for Full Text Search) was beneficial to get started, now I think I want to take the next step with Full Text Search to see if it can functionally meet some application needs that we have been struggling with for some time.

Solution
Well let's just dive into examples a few different SQL Server 2005 examples with the AdventureWorks sample database to see if these will meet your needs. Although Full Text Search has 4 common commands to access the Full Text Catalogs, let's try to dive into a number of different options with the CONTAINS command and see if we can cover many of the variations.

CONTAINS - Syntax Options
CONTAINS
( { column_name | (column_list) | * }
, '<>'
[ , LANGUAGE language_term ]
)
<> ::=
{ <>
| <>
| <>
| <>
| <>
}
| { ( <> )
[ { <> | <> | <> } ]
<> [ ...n ]
}
<> ::=
word | " phrase "
<> ::=
{ "word * " | "phrase *" }
<> ::=
FORMSOF ( { INFLECTIONAL | THESAURUS } , <> [ ,...n ] )
<> ::=
{ <> | <> }
{ { NEAR | ~ }
{ <> | <> }
} [ ...n ]
<> ::=
ISABOUT
( { {
<>
| <>
| <>
| <>
}
[ WEIGHT ( weight_value ) ]
} [ ,...n ]
)
<> ::=
{ AND | & }
<> ::=
{ AND NOT | & !}
<> ::=
{ OR | | }


Source - SQL Server 2005 Books Online - CONTAINS

CONTAINS - OR Logic

In this query the terms 'nut', 'screw' or 'washer' are returned. As a note, the '*' in the first parameter of the CONTAINS statement indicates that all columns in the table registered with Full Text Search should be searched in this query.USE AdventureWorks;
GO
SELECT *
FROM Production.Product
WHERE CONTAINS(*, '"*nut*" OR "*screw*" OR "*washer*"');
GO


CONTAINS - AND Logic

In the first example query just the Name column is queried for the terms 'flat' and 'washer'. In the second example, the term 'nut' is searched without 'hex', returning results such as 'chainring nuts', 'lock nuts', etc. These are both examples of two independent terms being included in the search criteria.-- AND Logic
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"flat" AND "washer"');
GO

-- AND NOT Logic
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"nut" AND NOT "hex"');
GO


In this example, the only difference is that the phrase 'flat washer' is queried as opposed to two separate terms.USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"flat washer"');
GO


As a note for these two specific queries with the original data set, these two queries return the same results, but with a different data set, that may not be the case because the two independent terms could be more prevalent than the single phrase (combination of both terms).

CONTAINS - Prefix

In this example, the prefix 'chain' is queried which returns the word 'chain', but also matches on a single word like 'chainring' and the phrase 'chainring bolts'.USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"chain*"');
GO


CONTAINS - Proximity

In the first example, the NEAR keyword is used to find the word 'men' near 'shorts'. In the second example, three words are used. As a point of reference, the order of the words does not seem to generate different result sets.-- 2 terms
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'men NEAR shorts');
GO

-- 3 terms
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'XL NEAR men NEAR shorts');
GO


CONTAINS - Inflection and Thesaurus

The first example is inflectional which means that the various forms of 'shift' will be returned, such as 'shifted', 'shifting', etc. The second example is based on the SQL Server Thesaurus setup in the SQL_Server_install_path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\ directory. These are a set of XML files that can be customized with synonyms for terms specific to your application.-- INFLECTIONAL
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, shift) ');
GO

-- THESAURUS
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (THESAURUS, wash) ');
GO


CONTAINS - Weight

In this example, the weight of the terms 'nut', 'bolt' and 'washer' are selected based on the weight in that order.USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'ISABOUT (nut weight (.8),
bolt weight (.4), washer weight (.2) )' );
GO


CONTAINS - Variable

In the two examples below, variable strings are passed into the CONTAINS command. Based on my testing, as long as the strings have the proper syntax then just about anything can be passed with the variable, which makes the programming a little easier from front end search interfaces.-- Example 1
USE AdventureWorks;
GO
DECLARE @Parm1 varchar(50)
SET @Parm1 = 'XL NEAR men NEAR shorts'
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], @Parm1);
GO

-- Example 2
USE AdventureWorks;
GO
DECLARE @Parm1 varchar(50)
SET @Parm1 = '"XL" OR "men" OR "shorts"'
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], @Parm1);
GO