10

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".

Improve this question
7
  • 1
    Instead of trying to verify the input, just make sure to escape special characters in the string. Commented Sep 15, 2011 at 9:53
  • 1
    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". Commented Sep 15, 2011 at 9:58
  • 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. Commented Sep 15, 2011 at 10:01
  • 1
    @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. Commented Sep 15, 2011 at 13:07
  • 2
    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.) Commented Aug 28, 2015 at 8:18

5 Answers 5

Reset to default
9

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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]*?(?=*)*\/)
8

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)

Improve this answer

6 Comments

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.
/[a-zA-Z0-9.,?]*/ will match as true for any string only containing those characters, false if there are other characters.
escape all input strings, and use parametrized queries.
@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).
@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 :)
|
4

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

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

1 Comment

What if non-Latin character sets are permitted?
1

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.