Removing unwanted characters from string using javascript regex for feeding into MYSQL statement

Question

Hi I need to remove some unwanted characters from a string, I'm trying to make a regex to match the required characters but I feel like I'm miles off and don't want to create false positives. Some help would be very much appreciated.

The starting string looks like:

'SELECT \t* \nFROM \tAudit_Log a WHERE changedate > \'2016-07-21T18:51:41.900Z\''

however this doesn't get parsed correctly by MYSQL, so it needs to look like:

'SELECT * FROM \tAudit_Log a WHERE changedate > '2016-07-21T18:51:41.900Z''

I've tried this but not having much luck:

str = str.replace('\t', '');
str = str.replace('\n', '');
str = str.replace('\\', '');

What is an escape character \t doing in a sql statement? And, what if it's some other letter? — user557597
– user557597, Commented Jul 21, 2016 at 18:02
Realize that you are still vulnerable to SQL injections. NEVER trust ANYTHING from users. Always sanitize your input on the Server side. You can clean, to ensure that the content is worth being processed, but please, do know that is very easy to alter any request, from the client side. — Bonatti
– Bonatti, Commented Jul 21, 2016 at 18:04
sorry should clarify this is on the node end and the statements don't come from the user — Craig Harley
– Craig Harley, Commented Jul 21, 2016 at 18:05

score 1 · Accepted Answer · 2016-07-21 18:26:42Z

1

If escape plus a-z is the culprit, simplify it to two cases:

Escape + not a-z nor escape
Escape + a-z or escape

In the first case, the not a-z (punctuation) is written back.
In the second case, its not.

Find: (?i)\\(?:([^\\a-z])|[\\a-z])?
Replace: $1 or \1

Expanded

 (?i)                   # Case insensitive
 \\                     # '\' To be removed
 (?:                    # Cluster start
      ( [^\\a-z] )           # (1), Punctuation written back
   |                       # or,
      [\\a-z]                # The rest, to be removed
 )?                     # Cluster end, and is optional for EOS

edited Jul 21, 2016 at 18:26

answered Jul 21, 2016 at 18:19

user557597

Sign up to request clarification or add additional context in comments.

Comments

Zohaib Ijaz · Accepted Answer · 2016-07-21 18:01:53Z

0

Here is a solution

var str =  'SELECT \t* \nFROM \tAudit_Log a WHERE changedate > \'2016-07-21T18:51:41.900Z\'';
str = str.replace(/\t|\n/g, '').replace(/\'/g, "'")

edited Jul 21, 2016 at 18:01

answered Jul 21, 2016 at 17:57

Zohaib Ijaz

23k7 gold badges43 silver badges63 bronze badges

3 Comments

Craig Harley Over a year ago

This still ends up looking like: SELECT * \nFROM Audit_Log a WHERE changedate > \'2016-07-21T19:00:56.741Z\'

Zohaib Ijaz Over a year ago

Updated my answer.. missed \n

Craig Harley Over a year ago

Wonderful thankyou, however now it's just the backslashes, replace('\', '') doesnt seem to work

David R · Accepted Answer · 2016-07-21 18:05:10Z

0

Try this,

str.replace(/[\n\t\r]/g,"")

answered Jul 21, 2016 at 18:05

David R

15.7k8 gold badges59 silver badges74 bronze badges

2 Comments

Blue Over a year ago

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, as this reduces the readability of both the code and the explanations!

David R Over a year ago

@FrankerZ I agree with you. Apologies for that!.

Craig Harley · Accepted Answer · 2016-07-21 18:13:51Z

0

None of the other solutions gave the desired result, the pattern I ended up using is:

(\\t|\\n|\\)

answered Jul 21, 2016 at 18:13

Craig Harley

3311 gold badge5 silver badges18 bronze badges

Collectives™ on Stack Overflow

Removing unwanted characters from string using javascript regex for feeding into MYSQL statement

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related