8

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

Bart Kiers
  • 166,582
  • 36
  • 299
  • 288
Russell Parrott
  • 873
  • 7
  • 19
  • 38
  • 1
    Instead of trying to verify the input, just make sure to escape special characters in the string. – brain Sep 15 '11 at 09: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". – Qtax Sep 15 '11 at 09: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. – ewanm89 Sep 15 '11 at 10:01
  • @brain 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. – sillyMunky Sep 15 '11 at 12:47
  • 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. – brain Sep 15 '11 at 13:07
  • 1
    **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.) – Chop Aug 28 '15 at 08:18
  • There's rather a lot wrong with the email validator too. – symcbean Aug 28 '15 at 08:26

5 Answers5

8

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
Nery Jr
  • 3,849
  • 1
  • 26
  • 24
7

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)

sillyMunky
  • 1,260
  • 8
  • 13
  • 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. – Russell Parrott Sep 15 '11 at 09:56
  • /[a-zA-Z0-9.,?]*/ will match as true for any string only containing those characters, false if there are other characters. – sillyMunky Sep 15 '11 at 10:00
  • escape all input strings, and use parametrized queries. – ewanm89 Sep 15 '11 at 10:03
  • @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). – sillyMunky Sep 15 '11 at 10:04
  • @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 :) – Russell Parrott Sep 15 '11 at 10:10
  • @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. – sillyMunky Sep 15 '11 at 12:52
4

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

^([a-z]|[A-Z]|[0-9]){4,8}$
Bengi Besçeli
  • 3,638
  • 12
  • 53
  • 87
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.

Community
  • 1
  • 1
Tomas
  • 57,621
  • 49
  • 238
  • 373
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.

ewanm89
  • 919
  • 5
  • 22