MySQL Cookbook, 3rd Edition

Chapter 12. Validating and Reformatting Data

Introduction

The previous chapter, Chapter 11, focuses on methods for moving data into and out of MySQL. The present chapter is related in that it also covers data transfer issues, but here the emphasis is on issues of datafile content rather than structure. For example, if you don’t know whether the values contained in a file are legal, preprocess it to check or reformat them. Numeric values may need verification as lying within a specific range, dates may need conversion to or from ISO format, and so forth.

The chapter deals with formatting and validation issues primarily within the context of checking entire files, but many of the techniques discussed here can be applied in other situations as well. Consider a web-based application that presents a form for a user to fill in and then processes its contents to create a new row in the database. Web APIs generally make form contents available as a set of already parsed discrete values, so the application may not need to deal with record and column delimiters. On the other hand, validation issues remain paramount. You really have no idea what kind of values a user is sending your script, so it’s important to check them. This chapter covers validation extensively, and Validating Web Input revisits the issue in web context.

For additional background on the material covered here, see the introduction to Chapter 11.

Source code for program fragments and scripts discussed in this chapter is located ...