Search code examples
mysqlspecial-characters

Are there any scope that escape all special characters in mysql query?

I have a set of queries with randoms data that i want to insert in database. Randoms data may have any special characters. for example: 

INSERT INTO tablename VALUES('!^'"twco\dq');

Are there any scope that escape all special characters?

please help.

Solution

  • No, there is no "scope" in MySQL to automatically escape all special characters.

    If you have a text file containing statements that were created with potentially unsafe "random values" like this:

    INSERT INTO tablename VALUES('!^'"twco\dq');
                              ^^^^^^^^^^^

    You're basically screwed. MySQL can't unscramble a scrambled egg. There's no "mode" that makes MySQL work with a statement like that.

    Fortunately, that particular statement will throw an error. More tragic would be some nefariously random data, 

    x'); DROP TABLE students; --

    if that random string got incorporated into your SQL text without being escaped, the result would be:

    INSERT INTO tablename VALUES('x'); DROP TABLE students; --');

    The escaping of special characters has to be done before the values are incorporated into SQL text.

    You'd need to take your random string value: 

    !^'"twco\dq

    And run it through a function that performs the necessary escaping to make that value safe for including that as part of the the SQL statement.

    MySQL provides the real_escape_string_function as part of their C library. Reference https://dev.mysql.com/doc/refman/5.5/en/mysql-real-escape-string.html. This same functionality is exposed through the MySQL Connectors for several languages.

    An even better pattern that "escaping" is to use prepared statements with bind placeholders, so your statement would be a static literal, like this:

    INSERT INTO tablename VALUES ( ? )