javascript remove bad characters for MySQL db hit

crmpicco · February 24th, 2006, 05:26 AM

Code:

function fix_chars(id,val)
{
    if ((typeof(val)=="undefined")||(typeof(id)=="undefined")){
        return;
    }
    if(/'/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/'/g,'');
    }
    if(/&/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/&/g,'');
    }
    if(/_/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/_/g,'');
    }
    if(/,/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/,/g,'');
    }
    if(/%/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/%/g,'');
    }
    if(/`/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/`/g,'');
    }
    if(/"/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/"/g,'');
    }
    if(/@/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/@/g,'');
    }
    if(/~/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/~/g,'');
    }
    if(/#/.test(val))
    {
        document.forms["fexp"].elements[id].value = val.replace(/#/g,'');
    }
}

<input type="text" name="depapt" id="depapt" onKeyUp="fix_chars('depapt',this.value);" />

Is there anyway to cut this function 'fix_chars' down? Am i missing something?
My object is basically to remove any bad characters to stop it crashing my MySQL Database.

Picco

www.crmpicco.co.uk

ChrisScott · February 24th, 2006, 05:48 AM

Hi Picco,

You could do something like this...

Code:

function fix_chars(textBox)
{
    textBox.value = textBox.value.replace(/['&_,%`"@~#]/g, "");
}
...
<input type="text" name="depapt" id="depapt" onKeyUp="fix_chars(this);" />

However, IMHO you should not rely on client side code to prevent db crashes as this can easily be disabled.

Also, these characters should not really be crashing your db.

HTH,

Chris

crmpicco · February 24th, 2006, 09:31 AM

thanks Chris, it was mainly the & and the ' that would crash the db, i was just being sure that the others wouldnt by filtering them out......

www.crmpicco.co.uk

crmpicco · February 24th, 2006, 09:39 AM

BTW, what characters WOULD crash my DB? the & and ' for sure. but what out of my function and what ive not got would crash, or cause problems with my db. thanks for your help chris

www.crmpicco.co.uk

ChrisScott · February 24th, 2006, 09:52 AM

If we're talking text fields here, you should be able to insert all the above characters into a MySQL db.

Can you post the sql that's causing the problems?

Cheers,

Chris

crmpicco · March 1st, 2006, 01:15 PM

Code:

choice = request("choice")
set rs2=con.execute("select * from db_stadiumname where countryname like '"&choice&"%' and languagecode = 'gb'")

Choice is coming from a text box in the previous page.

What happens is that if a bad character, for example, an apostrophe is entered and submitted then it crashed the MySQL hit and the DB.

Is this not fairly common surely, that is the reason i have built CS and SS validation to catch this...

Picco

www.crmpicco.co.uk

ChrisScott · March 1st, 2006, 06:21 PM

You just need to replace apostrophes with two apostrophes e.g.

Code:

choice = request("choice")
If choice <> "" Then
    choice = Replace(choice, "'", "''")
End If

HTH,

Chris

crmpicco · March 2nd, 2006, 10:14 AM

just wondering Chris, you mentioned that most of those chars SHOULDNT crash my db, but surely the apos and ampersand and classic MySQL crashing characters. are you thinking of something else?

www.crmpicco.co.uk

ChrisScott · March 2nd, 2006, 10:30 AM

Hi Picco,

I insert apostrophes and ampersands all the time without any problems.

Cheers,

Chris

crmpicco · March 6th, 2006, 08:02 AM

what SS-code are you using?

www.crmpicco.co.uk