One of the biggest questions that people come to me regarding SQL statements is when the data that they need to store in the database contains a single quote (my dog's name is scoobie). Normally, if you were to do a simple select statement on a database it would give you an error. This is because in most popular databases the single quote is used as a text delimiter. That is, it is used to mark the start and end of a string.



SELECT * FROM authors WHERE fname = 'nothing'

Above, the value "rob" is delimited by the single quotes. Now if we attempted:

SELECT * FROM authors WHERE comments = 'my dog's name is scoobie'

You can see the obvious mistake. - comments = 'my dog'

The rest of the string will generate an error.

SOLUTION:
The solution is simple. What we need to do is double up the single qutoes. So, from our example above,

SELECT * FROM authors WHERE comments = 'my dog's name is scoobie'

Now becomes
SELECT * FROM authors WHERE comments = 'my dog''s name is scoobie'

The replace function will enable us to do this very easily.

The easiest way to solve this problem is to include a simple function in our utilities.inc file:


Function sqlEncode(sqlValue)
     sqlEncode = replace(sqlValue, "'", "''")
End function

Now whenever we build any sql statements all we need to do is use the sqlEncode function to encode our data.

sql = "SELECT * from FROM WHERE comments = '" & sqlEncode(strComments) & "'"


Author:
Robert Chartier
nothingmn@netexecutive.com
October 2, 1999

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Database Code Articles
More By aspfree

 

developerWorks - FREE Tools!

NEW! Driving Business Success with Rational Process Library Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance. FREE! Go There Now!

NEW! Best Practices: The Integrated Project and Portfolio Management Platform. Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan. FREE! Go There Now!

NEW! Best practices for software analysis: An introduction to the IBM Rational Software Analyzer application This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer. FREE! Go There Now!

NEW! BlammoSplat: Build a community Web site of OpenLaszlo animations, Part 3: The community animation Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo. FREE! Go There Now!

NEW! Evaluate WebSphere Extended Deployment Compute Grid V6.1 Visit IBM developerWorks to download a free trial version of WebSphere Extended Deployment Compute Grid, which lets you schedule, execute, and monitor batch jobs. Because online transaction processing and batch jobs execute simultaneously on the same server resources, you can avoid costly duplication of resources. Compute Grid supports job types of Java transactional batch, compute-intensive and a new type called "native execution", which enables non-Java workloads to run on distributed end points. FREE! Go There Now!

NEW! Rational Testing eKits Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing. FREE! Go There Now!

NEW! Test terminal-based applications with Rational Functional Tester Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily. FREE! Go There Now!

NEW! Webcast: IBM Rational Build Forge - Beyond the Build The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size. FREE! Go There Now!

NEW! Webcast: Quickly provide customized, integrated user interfaces with Lotus Notes 8 IBM Lotus Notes 8 provides a wide range of developers the ability to provide customized, integrated user interfaces via composite applications and via custom sidebar and toolbar plug-ins. This webcast provides you with tips and techniques to use with out-of-the-box capabilities of Lotus Notes 8, and survey how you can share useful components within your own company and within a larger community. FREE! Go There Now!

NEW! Webcast: Striking the right balance between manual and automated testing Join this webcast to learn how IBM Rational's Functional Testing solution enables you to implement automation your way, at your pace, with your existing staff. In this webcast, you’ll learn how you can eliminate redundancy of manual test scripts, reduce errors, and increase test coverage through test automation. After this presentation you will understand how IBM Rational Functional Testing solution can streamline your manual testing and make test automation easily attainable. FREE! Go There Now!

All FREE IBM® developerWorks Tools!