Tips on SQL queries and connection strings

In case your connection string or SQL query does not work as expected, have in mind the below:

  1. Replace any single quote (') characters with double quotes (") in your input values (or vice versa).

    For example:

    %Input% = the message' SELECT * FROM logs WHERE message = '%Input%';

    The above query would produce an error due to the single quote at the end of the input value.

    In order to resolve it, modify the input variable accordingly via the "Replace Text" action - Text to Parse: %Input% | Text to Find: ‘ | Replace with: ” | Store result text into %Input%.

    Remove single quote character

  2. Escape any percentage characters (%) via the backslash character (\).

    Escape special characters

    Refer to this support article on usage of % character for more information.

  3. Check for spaces and typos in the connection string, in order to ensure its validity.

    For example in the following connection string we’ve replaced the values, with the variables that we’d be using. Now the below string looks fine, doesn’t it?

    Driver={MySQL ODBC 5.1 Driver}; Server=%Server%; Database=%DatabaseName%; User=%Username%; Password=%Password%; Option ;

    Well... it’s not! Notice there’s a space on “option ”. This will cause the query to not run.

    So, always double check the string’s syntax for misspells and typos.

    In general, have in mind that spaces can be removed via the "Replace Text" action, by replacing the space character %" "% with the empty string character %""%.

    Remove spaces using replace text action

  4. Verify that no firewall is blocking the connection to the database. If you’re getting an error, and you’ve got problems trying to locate it, just disable the firewall for a few seconds, and give it a try, it might save you a fair amount of time!

Additionally, feel free to visit this SQL Tutorial. It offers relevant examples and exercises which can help you experiment and brush up your SQL skills.