escape input for mysql server

[tpf80]

in Qt is there any equivalent function similar to "mysql_escape_string()" in PHP?

what I am trying to accomplish is to allow a user to enter information into a QTextedit and then transfer the data into a blob in mysql. My code so far works perfectly to get the text edit info into the blob except for cases where certain special characters are entered. For instance entering:

somestuff","somemorestuff

into the text edit causes an error as my query interprets this as part of the SQL statement rather than something the user typed that should be put in the blob.

[patrik08]

in Qt is there any equivalent function similar to "mysql_escape_string()" in PHP?
what I am trying to accomplish is to allow a user to enter information into a QTextedit and then transfer the data into a blob in mysql. My code so far works perfectly to get the text edit info into the blob except for cases where certain special characters are entered. For instance entering:
into the text edit causes an error as my query interprets this as part of the SQL statement rather than something the user typed that should be put in the blob.

PHP having 5000 or more function. C++ is different...


For any projekt i make a base class to extendend on evry place i need....

I work so many on XML utf-8 and i put on sql as encodeBase64 and must not quote...
php can read on http://php.net/base64-decode.php and the Dom XML friends....
As caching Dom ist faster.... only must transform xslt ...the data pdf, word, openoffice, svg, csv, ecc...

/* insert quote */
QString sql_Quote( QString xml )
{
   QString text = xml;
   text.replace("\"","\\\"");  /* 228 ``*/
   text.replace("'","`");
   return text;
}
 
/* select quote */
QString sql_DeQuote( QString xml )
{
  QString text = xml;
   text.replace("\\\"","\"");  /* 228 ``*/
   text.replace("`","\'");
  return text;
}
 
 
/* encode to name */
QString encodeBase64( QString xml )
{
    QByteArray text;
    text.append(xml);
    return text.toBase64();
}
 
/* decode to name */
QString decodeBase64( QString xml )
{
    QByteArray xcode("");;
    xcode.append(xml);
    QByteArray precode(QByteArray::fromBase64(xcode));
    QString notetxt = precode.data();
    return notetxt;
}

/* insert quote */
QString sql_Quote( QString xml )
{
   QString text = xml;
   text.replace("\"","\\\"");  /* 228 ``*/
   text.replace("'","`");
   return text;
}

/* select quote */
QString sql_DeQuote( QString xml )
{
  QString text = xml;
   text.replace("\\\"","\"");  /* 228 ``*/
   text.replace("`","\'");
  return text;
}


/* encode to name */
QString encodeBase64( QString xml )
{
    QByteArray text;
    text.append(xml);
    return text.toBase64();
}

/* decode to name */
QString decodeBase64( QString xml )
{
    QByteArray xcode("");;
    xcode.append(xml);
    QByteArray precode(QByteArray::fromBase64(xcode));
    QString notetxt = precode.data();
    return notetxt;
}

To copy to clipboard, switch view to plain text mode 

is your QTextedit data xhtml? image and so.....


IMO: QTextedit xhtml and image or attachment i zip the file to mysql

blob (mysql longblob 2GB max 4MB query setting) data zip file

QByteArray zipstream;
zipstream.toBase64();
and after to replace field.... is so clean and small data...

SAMPLE: my new Home-Page http://www.swisse.net/ work only on 2 xslt file
http://ciz.ch/map/theme/zest/page.xsl
http://ciz.ch/map/theme/zest/master_html.xsl
PHP unzip data as cache folder , like this http://www.swisse.net/data/2007/01/01/50000/ and xslt transform all no sql query... only admin running sql.

[wysota]

Try preparing the query and then binding values (QSqlQuery::prepare() and QSqlQuery::bindValue()), they should escape the data for you.

[patrik08]

Try preparing the query and then binding values (QSqlQuery::prepare() and QSqlQuery::bindValue()), they should escape the data for you.

Yes work fine on sqlite or other db type != not on mysql Collation special like CHARSET=utf-8 or other koi8r .... qt take from CHARSET QLocale and pusch on DBs and not read "SHOW COLUMNS FROM TABLE" to become the correct CHARSET, if table having iso latin 1 an OS QLocale is the same works....

[tpf80]

thanks for your help, "bindValue()" is the way to go in this instance. am now able to add records with non-standard characters in them and it works fine.

[tpf80]

I also noticed that I can use "bindValue()" for select statements, even though the examples only show them for inserts:

query.prepare("select userid, username, firstname, lastname, email, status from user where username=:username");
query.bindValue(":username", login.username);
if (!query.exec()) {
    //do something to handle the error here
} else {
    while (query.next()) {
        //do something with the results we got here
    }
}
]

query.prepare("select userid, username, firstname, lastname, email, status from user where username=:username");
query.bindValue(":username", login.username);
if (!query.exec()) {
    //do something to handle the error here
} else {
    while (query.next()) {
        //do something with the results we got here
    }
}
]

To copy to clipboard, switch view to plain text mode 

is this a proper use of "bindValue()"?

[wysota]

Yes work fine on sqlite or other db type != not on mysql Collation special like CHARSET=utf-8 or other koi8r .... qt take from CHARSET QLocale and pusch on DBs and not read "SHOW COLUMNS FROM TABLE" to become the correct CHARSET, if table having iso latin 1 an OS QLocale is the same works....

If you have the database set incorrectly then don't expect everything to work.

I also noticed that I can use "bindValue()" for select statements, even though the examples only show them for inserts:

(...)

is this a proper use of "bindValue()"?

Sure

[tpf80]

One last question I notice that:

#
query.prepare("select userid, username, firstname, lastname, email, status from user where username=:username");
#
query.bindValue(":username", login.username);

#
query.prepare("select userid, username, firstname, lastname, email, status from user where username=:username");
#
query.bindValue(":username", login.username);

To copy to clipboard, switch view to plain text mode 

Pretty much escapes the login.username, puts it in quotes, and inserts it where :username is. However how do I use bindValue() with queries using "like" for example:

query.prepare("select userid, username, firstname, lastname, email, status from user where username like \"%" + something + "%\"");

query.prepare("select userid, username, firstname, lastname, email, status from user where username like \"%" + something + "%\"");

To copy to clipboard, switch view to plain text mode 

it doesn't seem that

query.prepare("select userid, username, firstname, lastname, email, status from user where username like \"%:something%\"");
query.bindValue(":something", somevariable);

query.prepare("select userid, username, firstname, lastname, email, status from user where username like \"%:something%\"");
query.bindValue(":something", somevariable);

To copy to clipboard, switch view to plain text mode 

works properly. (gives an error preparing the query). What is the proper way to use bindvalue with a "like" query?

[jacek]

What is the proper way to use bindvalue with a "like" query?

Try:

SELECT userid, username, firstname, lastname, email, STATUS
FROM user
WHERE username LIKE '%' || :something || '%'

select userid, username, firstname, lastname, email, status
from user
where username like '%' || :something || '%'

To copy to clipboard, switch view to plain text mode 

or

query.bindValue(":something", "%" + somevariable + "%" );

query.bindValue(":something", "%" + somevariable + "%" );

To copy to clipboard, switch view to plain text mode