MySQL special characters

[hairsoup]

Does anyone know what characters I should check for in a users input that might cause an error in a MySQL query? I've already identified the single quote, but wondering is theres any others?

P.s. sorry for my poor question:advice ratio on here lately, been very busy.

[crus]

http://uk2.php.net/manual/en/function.mysql-real-escape-string.php

[Coding Monkey]

You really should just buy a book on MySQL. Also, DevShed is a far more suitable place to ask programming questions. You're 99.9% likely to find the question has already been asked, or almost certain to get an answer from someone in the MySQL forum really quick.

[Enigma121]

As more general advice, you should try to avoid passing user input from a web application / program directly to MySQL without very good validation.

Your SQL statements should be preparsed in whatever high level language you are using.

If possible you should also be restricting data values using pick lists, so the data is drawn from the database, rather than user entered.

I'd prefer to apply the reverse restriction and only allow characters that are valid for your particular field.

For example if you have a eight character varchar field which you are using in a where clause, ensure that length is correct, it only contains A-Z or a-z or numbers. Control this in your application and you don't have any worries.

[crus]

As Enigma says,

use java script on the users machine to validate the data entered, then look to lock down the data coming in beyond the final clean by mysql.

The lower parts of the mysql website pages often have excellent examples to push you in the right direction (hint).

D

[hairsoup]

Crus> Thanks but I'm working in ASP.

Mac> Other forums are such a hassle compared to this one. I also have a book on SQL which doesnt cover some of the questions I've had to ask lately.

Enigma> Sounds good (yet worrying), went over my head a bit, could you simplify it a bit please?

[Enigma121]

As Enigma says,

use java script on the users machine to validate the data entered, then look to lock down the data coming in beyond the final clean by mysql.

The lower parts of the mysql website pages often have excellent examples to push you in the right direction (hint).

D

Don't agree with this Crus. Nasty hackers will turn javascript off... Bit of a worry.

Instead, use your ASP code to do the validation before passing anywhere near the database.

[Enigma121]

Err,

In English then...

Use prepared statement objects (or whatever ASP's equivalent is, sorry I don't know exact structure - I'm a JSP consultant).

This improves performance and security of the application. The SQL will be processed once by MySQL then run over and over again.... sweet.

Only use a text box where the data hasn't been entered in the application before. If it has, you should be using a SELECT OPTION structure in HTML.

[Coding Monkey]

Mac> Other forums are such a hassle compared to this one. I also have a book on SQL which doesnt cover some of the questions I've had to ask lately.


Trust me, Devshed. You cannot go wrong. I'm on there helping out a lot.

Also, if the book doesn't cover them, it's really not a very good book. I'm not criticising you, but the book, as they're basic questions.

[webit]

Err,

In English then...

Use prepared statement objects (or whatever ASP's equivalent is, sorry I don't know exact structure - I'm a JSP consultant).

This improves performance and security of the application. The SQL will be processed once by MySQL then run over and over again.... sweet.

Only use a text box where the data hasn't been entered in the application before. If it has, you should be using a SELECT OPTION structure in HTML.

AGREED!!!

Keep all validation on the server and away from the database. I did see a very nice PHP way of doing it which for Enigma121's benifit didn't use Struts!! (I'm a J2EE developer BTW!). The form submitted to itself and if the validation was on (using PHP) then redirected to the next page in sequence.

Otherwise meet your new worst nightmare:

http://www.unixwiz.net/techtips/sql-injection.html

[Richard Conyard]

Unfortunately AFAIK ADO (the ASP database set of objects), do not have an equivalent to Prepared Statements, you can call Stored Procedures if the underlying database supports them (which I think later versions of mySQL do).

As mentioned Devshed is cool as is Sitepoint you might want to look in there since it's likely that someone has had the same problems and they have an answer in there.

[Enigma121]

Unfortunately AFAIK ADO (the ASP database set of objects), do not have an equivalent to Prepared Statements, you can call Stored Procedures if the underlying database supports them (which I think later versions of mySQL do).

As mentioned Devshed is cool as is Sitepoint you might want to look in there since it's likely that someone has had the same problems and they have an answer in there.

At last, a good security argument why everyone should be using JSP. I love it. :lol:

Calling stored procedures isn't quite the same as using prepared statements. If you build SQL dynamically in your code at all then you are at risk from SQL injection techniques. You need your SQL to fixed at compile time in order to be a bit safer.

Big RDBMS suppliers are always banging on about this sort of security risk in their products.

[Enigma121]

Ah well, that didn't last long. Here is a link to a prepared statement example for all you ASP heads.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_97qr.asp

[DuaneJackson]

Another point to bear in mind is that even if you restrict the values the user can post by use of a drop-down list or similar, you should still check and clean them at the back end because it's very easy to post other variables that were not pre-defined.

And never rely on the referer variable to check the data is coming from a 'safe' source. The referer header is incredibly easy to fake.

Thanks for that link Enigma, it will be very useful for me!

[Coding Monkey]

And never rely on the referer variable to check the data is coming from a 'safe' source. The referer header is incredibly easy to fake.


True, but still no harm in adding it although with other methods.

[hairsoup]

Thanks, some great advice in this thread, I was aware of SQL injection but to be honest I have neglected to protect myself against it, this sort of thing makes me glad that I dont give out the names of my sites ;)