Validating user input is really not complex at all. If you need to build and execute ad hoc queries you just need to remember two things.

With strings, always replace ticks/single-quotes (') with two ticks/single-quotes ('') <- notice there are two characters there and not a double-quote ("). If you do this then the user entry for that field will be accepted in its entirety and any SQL commands in the string, including comment markers.

Example:

User enters the following in password:

'; delete from users

You put the following code in the statement used to build SQL statement (VB 6)

sSQL = sSQL + "and password = '" & Replace (sPassword, "'", "''") & "'"

The resulting SQL statement is this:

select userid from users where userid = 'bogus' and password = '''; delete from users'

One other thing with strings. This is not so much a protection from SQL injection but will help keep errors from cropping up that could expose connection information in the form of an error response. Make sure you are limiting the size of strings in the UI to prevent people from entering text too large for the database to handle. If you are developing n-tier applications, you business layer should not trust the UI and double check the data.

For all non-string data you should use the appropriate means to determine if the input is valid for the data type that is to receive the input. If the field is an integer, check to see that the user entered an integer; same with dates, etc. Also don't assume that client-side form validation in web applications will catch this. Always check on the server-side because there is no guarantee that the user is using the form you created!