Escape a string for mysql input.

[litzinger]

Hello I'm creating a backend for an AI I'm in the process of developing, and I want to get a reply from cleverbot when my AI doesn't know what to say.

I'm doing this using a mysql table to store the inputs and outputs, however, sometimes the reply from the cleverbot api includes single or double quotes.

Is there a way to escape these responses with a regular expression before inputting them into the database?

[jchd]

StringReplace or StringRegexpReplace.

$strOK = StringReplace($str, '"', '""')
$strOK = StringReplace($strOK, "'", "''")

or

$strOK = StringRegexpReplace($str, '(''|")', '$1$1')

 

[genius257]

or use bind, if you have access to the part of the code that inserts the data into the DB, that way it does not matter if it contains any single or double quotes.

Quote

As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that enables you to insert special markers into a statement string, and then bind data values to them when you issue the statement. In this case, the API takes care of escaping special characters in the values for you.

source: https://dev.mysql.com/doc/refman/5.7/en/string-literals.html

[jchd]

The issue with binding is that we have to make many invokations of the binding API (depends on the number of fields you're dealing with in the table), which is generally slower from AutoIt than handcrafting a whole SQL statement.