regex expressions prevent sql/script injection

Question

I am trying to create a regex expression for client side validation (before server side validation which will also take place) to prevent sql/script injection i.e something like this - which does not work

(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)

What is the correct format for this (above) expression so I can get it to work?

e.g. my EMail checker is like this

(/^[^\\W][a-zA-Z0-9\\_\\-\\.]+([a-zA-Z0-9\\_\\-\\.]+)*\\@[a-zA-Z0-9_]+(\\.[a-zA-Z0-9_]+)*\\.[a-zA-Z]{2,4}$/))

Oh and if you can think of anything else to add please "shout".

Valerlan

Instead of trying to verify the input, just make sure to escape special characters in the string. – Lineal

Why oh why? Use proper escaping facilities in your server side code to escape SQL parameters, and text that you are going to insert into HTML, or elsewhere. Preventing any injection possibilities and removing the need for any "injection prevention checks". – Adynamia

And it allows the user to add articles and the like that have SQL text in them. Consider, you may end up reusing this code later. – Kingcraft

@Lineal He doesn't even need to escape anything if he uses prepared statements. They're better in every way if you are doing a query which takes in user supplied parameters (or any query that is repeated multiple times). However, if that text is then read back out of the database, when it is read out he will have to do some escaping to prevent a stored XSS attack. – Inclinatory

@sillyMunky: You're right, no need to escape if you use prepared statements. My comment was if you want to query without using prepared statements. – Lineal

For security, please, please, keep in mind that, whatever you do on the client side, the input should always be (re)validated on the server side. It is always possible to intercept an HTTP request, change the values and thus totally bypass your client-side validation. Client-side validation is only for UX, so that the user can be warned of a possible mistake before submitting, thus avoiding repetitive and annoying exchanges with the server. (Yes, you wrote the server-side validation will be there, but it's so important I want others never to forget.) – Hispid

There's rather a lot wrong with the email validator too. – Berkly

Answer 1

Generally Sql Injection occurs in the strings passed to the parameters of a sql command such as insert, update, delete, or select. This regular expression validates whether there is any inline or block comment in the sql command.

/[\t\r\n]|(--[^\r\n]*)|(\/\*[\w\W]*?(?=\*)\*\/)/gi

Permalloy

This would not protect against the dreaded single quote. SillyMunky is though client side anybody can craft a nasty request just by adding breakpoints in the browser web developer tools. This would give you protection agaist dreader single quote server side: /'|[\t\r\n]|(--[^\r\n]*)|(\/*[\w\W]*?(?=*)*\/) – Speckle

Answer 2

You cannot in any way even hinder SQL injection attempts on the client side. It is a terrible, terrible idea which cannot help you but may cause a ball-ache for genuine users. It will not stop anyone who has a chance of actually exploiting an SQLi.

As far as the regex goes, you need to add the / at the beginning and end, like in your mail example, to denote it is a regex. Also, I think the regex design is flawed as it still allows many injection vectors. For example it allows the dreaded single quote ', -- comments and other. It doesn't even start to cover all the builtin functions of your RDBMS that might be knocking around. An attacker will often make use of, e.g. SELECT statements already on your server side, so removing them probably wouldn't help either.

Your best defense is to use parametrized queries on the server side (e.g. pg_prepare for php & postgres)

Inclinatory

OK put it another way I only want a-z A-Z 0-9 . , ? The genuine users of the site in question would not need to use words like script etc anyhow. – Valerlan

/[a-zA-Z0-9.,?]*/ will match as true for any string only containing those characters, false if there are other characters. – Inclinatory

escape all input strings, and use parametrized queries. – Kingcraft

@Russell, use client side validation if it helps you in some way, but honestly you have to stop thinking that client side check will improve security. Your design should assume that everyone's an attacker, and an attacker will be able to send any parameters they want with their own proxy (I recommend you play with burp proxy to see what I mean). – Inclinatory

@sillyM - To be honest I agree, but I have a very (stress) paranoid client as they have previously had a script & sql injection problem before. They are insisting on "belt and braces AND everything that is not really necessary" even though I do validate, filter & sanitize serverside - always have done. Oh injection not via my scripts :) – Valerlan

@Russell, I understand, I work in the industry myself. I would tell the client you have done all the sanitization required, use prepared statements, and focus your effort on ensuring there is no stored or reflected xss. Did you try the regex I suggested? It should achieve what you are after. Did it work for you? (trick question, I know it works :P ) If my answer was helpful, please accept/vote, cheers. – Inclinatory

Answer 3

Only a-z or A-Z or 0-9 between 4-8 characters:

^([a-z]|[A-Z]|[0-9]){4,8}$

Staley

What if non-Latin character sets are permitted? – Atreus

Answer 4

SQL injection and escaping sound magical to many people, something like shield against some mysterious danger, but: don't be scared of it - it is nothing magical. It is just the way to enable special characters being processed by the query.

So, don't invent new magial shields and ways how to protect the magical injection danger! Instead, try to understand how escaping of the input works.

Mason

Answer 5

It's more common to escape the control characters like `and ' that way one can still enter SQL code into the database, say it is on a CMS and I'm adding an article about SQL injection. I want to use those words and characters without triggering an injection. Looking at it, it seems to be for something with HTML base so convert the < and > to &lt; and &gt;, that will sanitize any and all html tags while still allowing HTML demo content to be displayed.

As already said, this should all be server side, as it comes into the system.

Kingcraft