How can I enable the user to enter reserved characters into the database with a combination of PHP, MySQL and AJAX?

Question

Hi, I've set up a website that allows the user to add places to a database. However, I am unfamiliar with regex and so I'm not sure how to allow characters like apostrophes and dollar signs without them affecting the database INSERT.

I have a form, which is read by AJAX and processed with PHP/MySQL so there's thee languages which have their own "reserved" characters for various uses. Realistically the regex needs to sit in my Javascript before the AJAX sends the string. I'm quite new to Javascript so I'm not sure how I'd go about this.

Is there any chance someone could help with this, but also explain how this regex works?

Cheers, Dan

Answer 1

You definitely shouldn't be doing this client-side in Javascript, anything client-side is subject to tampering and opens up an enormous SQL Injection security hole, which presumably you're familiar with if you're asking this question.

As far as I'm aware, the content of the string won't affect Javascript or PHP as it's, well, a string. It needs to be escaped before insertion into the database, have a look at the mysql_real_escape_string function.

Answer 2

Basically when you dealing with user-submitted data you cannot be aware enough. Imo putting the regex check into the JavaScript area is a bad idea or not enough. You should be sure about you filter these kind of data everywhere (and sufficiently).

Common filtering rules that apply:

• Control user input as much as you can (predefined variables, auto-suggest, etc.)
• Filter out any unwanted input like html/script tags with strip_tags.
• Use prepared statements or try to be familiar with PDO for example
• At least use mysql_real_escape_string before inserting to your database

Answer 3

No need to dabble in regex. Do the verification in the last possible step, here PHP, and use prepared statements in a well known and tested database library. Prepared statements will take care of properly quoting anything you throw at them.

A good example is MDB2

Answer 4

Simply put, you want to use prepared statements for the PHP/SQL portion. With PDO:

try {
    $dbh = new PDO($dsn,$user,$password);
    $stmt = $dbh->prepare('INSERT INTO users SET username = :user');
    $stmt->bindParam(':user',$_POST['user']);
    $stmt->execute();
    if ($stmt->rowCount == 1) {
        //success
    } else {
        //fail
    }
} catch (PDOException $e) { //Unhandled may expose $user/$password
    echo 'Database connection failed: ' . $e->getMessage();
}

and $_POST['user'] could be test'; DROP TABLE users; -- with no issue

Unfortunately, I don't quite remember how to do it with JS, but I advise against setting data with AJAX calls (JS disabled, NoScript, more clarity in the browser etc.)