[dordnung]

Escape a char from a String (With SQL support)

Because the stock to escape a string is only possible with a database handle and also limited on the used SQL driver, i write an little snippet to escape a char from a string. Also i added two methods to escape a string for MySQL and SQLite.
This can be also useful if you want to convert the database from a driver to another.

PHP Code:

/**
 * Escapes a string from a char with a given escaper char
 *
 * @param input            The string to escape
 * @param escape        Char to escape
 * @param escaper        Char to escape with
 * @param output        Output string to store escaped string
 * @param maxlen        Size of the output string
 *
 * @return                 Number of escaped chars 
 */
stock EscapeString(String:input[], escape, escaper, String:output[], maxlen)
{
    // Number of chars we escaped
    new escaped = 0;

    // Format output buffer to ""
    Format(output, maxlen, "");


    // For each char in the input string
    for (new offset = 0; offset < strlen(input); offset++)
    {
        // Get char at the current position
        new ch = input[offset];

        // Found the escape or escaper char
        if (ch == escape || ch == escaper)
        {
            // Escape the escape char with the escaper^^
            Format(output, maxlen, "%s%c%c", output, escaper, ch);

            // Increase numbers of chars we escaped
            escaped++;
        }
        else
        {
            // Add other char to output buffer
            Format(output, maxlen, "%s%c", output, ch);
        }
    }

    // Return escaped chars
    return escaped;
}

/**
 * Escapes string's ' or " chars with MySQL rules. Escape char is the \ char
 *
 * @param input            The string to escape
 * @param output        Output string to store escaped string
 * @param maxlen        Size of the output string
 * @param escape         True to escape ' char, false to escape " char
 *
 * @return                 Number of escaped chars 
 */
stock EscapeStringMySQL(String:input[], String:output[], maxlen, bool:escape = true)
{
    // Number of chars we escaped
    new escaped = 0;


    // Escape the ' char
    if (escape)
    {
        escaped = EscapeString(input, '\'', '\\', output, maxlen);
    }

    // Escape the " char
    else
    {
        escaped = EscapeString(input, '"', '\\', output, maxlen);
    }


    // Return escaped chars
    return escaped;
}


/**
 * Escapes string's ' or " chars with SQLite rules. Escape char is the ' or " char
 *
 * @param input            The string to escape
 * @param output        Output string to store escaped string
 * @param maxlen        Size of the output string
 * @param escape         True to escape ' char, false to escape " char
 *
 * @return                 Number of escaped chars 
 */
stock EscapeStringSQLite(String:input[], String:output[], maxlen, bool:escape = true)
{
    // Number of chars we escaped
    new escaped = 0;


    // Escape the ' char
    if (escape)
    {
        escaped = EscapeString(input, '\'', '\'', output, maxlen);
    }

    // Escape the " char
    else
    {
        escaped = EscapeString(input, '"', '"', output, maxlen);
    }


    // Return escaped chars
    return escaped;
} 


Here is a little test:

PHP Code:

#include <sourcemod>
#include <stringescape>


public OnPluginStart()
{
    // Just some strings to escape
    new String:firstString[] = "This is a normal string";
    new String:secondString[] = "Just use some ' chars, or more than one ' \'' '";
    new String:thirdString[] = "Just a \" \" escape it the \".. stupid \\\"\\\\\"";
    new String:fourthString[] = "We don't need the eeee, so escape them with a s";

    // Just Escape
    decl String:fourthOutput[sizeof(fourthString) * 2];

    // Output buffers mysql, twice size should be enough
    decl String:firstOutputMySQL[sizeof(firstString) * 2];
    decl String:secondOutputMySQL[sizeof(secondString) * 2];
    decl String:thirdOutputMySQL[sizeof(thirdString) * 2];

    // Output buffers sqlite, twice size should be enough
    decl String:firstOutputSQLite[sizeof(firstString) * 2];
    decl String:secondOutputSQLite[sizeof(secondString) * 2];
    decl String:thirdOutputSQLite[sizeof(thirdString) * 2];


    // Just Escape
    new escapedFourthChar = EscapeString(fourthString, 'e', 's', fourthOutput, sizeof(fourthOutput));


    // Now escape them :) First for mysql
    new escapedFirstCharMySQL = EscapeStringMySQL(firstString, firstOutputMySQL, sizeof(firstOutputMySQL));
    new escapedSecondCharMySQL = EscapeStringMySQL(secondString, secondOutputMySQL, sizeof(secondOutputMySQL), true);
    new escapedThirdCharMySQL = EscapeStringMySQL(thirdString, thirdOutputMySQL, sizeof(thirdOutputMySQL), false);

    // Now for sqlite
    new escapedFirstCharSQLite = EscapeStringSQLite(firstString, firstOutputSQLite, sizeof(firstOutputSQLite));
    new escapedSecondCharSQLite = EscapeStringSQLite(secondString, secondOutputSQLite, sizeof(secondOutputSQLite), true);
    new escapedThirdCharSQLite = EscapeStringSQLite(thirdString, thirdOutputSQLite, sizeof(thirdOutputSQLite), false);

    // Print result of first string
    PrintToServer("The first string '%s' is escaped for MySQL = '%s' (%i chars escaped) and for SQLite = '%s' (%i chars escaped)", 
            firstString, firstOutputMySQL, escapedFirstCharMySQL, firstOutputSQLite, escapedFirstCharSQLite);


    // Print result of second string
    PrintToServer("The second string '%s' is escaped for MySQL = '%s' (%i chars escaped) and for SQLite = '%s' (%i chars escaped)", 
            secondString, secondOutputMySQL, escapedSecondCharMySQL, secondOutputSQLite, escapedSecondCharSQLite);


    // Print result of third string
    PrintToServer("The third string '%s' is escaped for MySQL = '%s' (%i chars escaped) and for SQLite = '%s' (%i chars escaped)", 
            thirdString, thirdOutputMySQL, escapedThirdCharMySQL, thirdOutputSQLite, escapedThirdCharSQLite);

    // Print result of fourth string
    PrintToServer("The fourth string '%s' is escaped with 's' = '%s' (%i chars escaped)", 
            fourthString, fourthOutput, escapedFourthChar);

    /*
    Output:
    
    The first string 'This is a normal string' is escaped 
        for MySQL = 'This is a normal string' (0 chars escaped) and for SQLite = 'This is a normal string' (0 chars escaped)
    
    The second string 'Just use some ' chars, or more than one ' '' '' is escaped 
        for MySQL = 'Just use some \' chars, or more than one \' \'\' \'' (5 chars escaped) and for SQLite = 'Just use some '' chars, or more than one '' '''' ''' (5 chars escaped)
    
    The third string 'Just a " " escape it the ".. stupid \"\\"' is escaped 
        for MySQL = 'Just a \" \" escape it the \".. stupid \\\"\\\\\"' (8 chars escaped) and for SQLite = 'Just a "" "" escape it the "".. stupid \""\\""' (5 chars escaped)
    
    The fourth string 'We don't need the eeee, so escape them with a s' is escaped 
        with 's' = 'Wse don't nsesed thse sesesese, sso sesscapse thsem with a ss' (14 chars escaped)

    */
} 


I hope it's useful for you. Have fun

greetz Popoklopsi

[ecca]

Could be in use if you don't have a connection setup as you mentioned ;) Good job.

[asherkin]

It's idiotic to use this, if you need to insert text into a database, you're going to have a connection.
The driver-level functions are designed to escape exactly what's required.

[dordnung]

You don't always need escaped Strings for inserting. Maybe you just want to store a .sql file for importing (as i needed it, so i thought maybe someone else need it, too. Can't hurt...).

[ecca]

Asherkin, you maybe are inexperienced with php and still want to send information against a php code and then this will be functional.

Edit: You maybe not should be throwing you into deep water before you can swim.

[Zephyrus]

Originally Posted by ecca Asherkin, you maybe are inexperienced with php and still want to send information against a php code and then this will be functional. Edit: You maybe not should be throwing you into deep water before you can swim.

using mysql_real_escape_string in PHP is easier than in sourcemod lol

[dordnung]

If someone need it, you can use it, if you don't need it, don't use it...

[zipcore]

good job, Danke

[alongub]

Originally Posted by Popoklopsi You don't always need escaped Strings for inserting. Maybe you just want to store a .sql file for importing (as i needed it, so i thought maybe someone else need it, too. Can't hurt...).

If you're exporting a SQL file from a SM plugin, I strongly believe that you're doing something wrong.

[dordnung]

My experience is very good with it. E.g converting a sqlite database to a mysql file. A lot of people doesn't understand how it works. I converting a 180k rows database without any lagg. So where is the big problem? How whould you convert a sqlite databse to mysql? It's every userfriendly to offer some convertion command, especially from sqlite to mysql, because when you export it with a program it's escaped for sqlite and not for mysql and importing whouldn't work.

Sad to see sucha biased opinion, i just want to be friendly and offer it to the community, in the case somebody need it...