How to handle escaping quotes in mutations against SQL Datasource?

[Dlozitskiy]

Hello,

I have a question on how to handle escaping quotes when AppSync mutation runs against Aurora Serverless datasource.

This is the example from the reference:

{
    "version": "2018-05-29",
    "statements": [
        "insert into BOOKS VALUES (:AUTHOR, :TITLE, :ISBN13)",
        "select * from BOOKS WHERE isbn13 = :ISBN13"
    ],
    "variableMap": {
        ":AUTHOR": "$ctx.args.newBook.author",
        ":TITLE": "$ctx.args.newBook.title",
        ":ISBN13": "$ctx.args.newBook.isbn13"
    }
}

However if input parameters author or title contains a signle quote, this will break VTL.

What is the best way to work around this?

[katzeforest]

Is there any reason that input parameters are required to contain a single quote?

[Dlozitskiy]

Hi @katzeforest,

from the example above AUTHOR and TITLE are strings that may or may not contain quotes. For example Uncle Tom's Cabin by Harriet Beecher Stowe will throw an exception.

[katzeforest]

Have you tried these: https://www.databasestar.com/sql-escape-single-quote/

[Dlozitskiy]

Hi @katzeforest

I have tested your suggestions with the results below.

The challenging bit is to come up with the method which will work on all possible scenarios. Apart from quotes there may be a backslashes, double quotes, etc and I want to avoid doing search/replace for each scenario.

I'm wondering if there is a generic way to do an input sanitization in resolvers for RDS, similar to what $util.escapeJavaScript does.

Schema:

type Query {
	testRds: String
}

Query:

query testRds{
  testRds
}

Resolver test 1:

{
    "version": "2018-05-29",
    "statements": [
        "SELECT 'TEST #1' AS quoted_string FROM dual;"
    ]
}

Result: PASS

Resolver test 2:

{
    "version": "2018-05-29",
    "statements": [
        "SELECT 'TEST''#2' AS quoted_string FROM dual;"
    ]
}

Result: PASS

Resolver test 3:

{
    "version": "2018-05-29",
    "statements": [
        "SELECT q'[TEST'#3]' AS quoted_string FROM dual;"
    ]
}

Result: FAILED "message": "RDSHttp:{"message":"Unknown column 'q' in 'field list'"}"

Resolver test 4:

{
    "version": "2018-05-29",
    "statements": [
        "SELECT q'[TEST''#4]' AS quoted_string FROM dual;"
    ]
}

Result: Failed "message": "RDSHttp:{\"message\":\"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS quoted_string FROM dual' at line 1\"}"

Resolver test 5:

{
    "version": "2018-05-29",
    "statements": [
        "SELECT 'TEST\\'#5' AS quoted_string FROM dual;"
    ]
}

Result: PASS

Resolver test 6:

{
    "version": "2018-05-29",
    "statements": [
        "SELECT \"TEST'#6\" AS quoted_string FROM dual;"
    ]
}

Result: PASS

[ghost]

Unfortunately currently AppSync does not have generic sanitization feature . I will forward your request to the team.

[matwerber1]

+1 on this feature request. I see docs suggest as an alternative to use stored procedures in the database, but I'm really trying to keep the database as clean from SPs as possible, keep all query logic in AppSync.

[matwerber1]

Hi @katzeforest @Dlozitskiy, a regex wizard colleague of mine provided a pattern that I think works.

I placed it here: https://github.com/matwerber1/regex-to-detect-unescaped-single-quotes-for-sql/blob/master/README.md

Excerpt:

#set($myInputIsValid = !($util.matches("^.*(?<!')'('')*(?!').*$",$ctx.args.myInput)))

#if (!$myInputIsValid)
    $util.error("Input contains unescaped quote.")
#end

[herotangabc]

the following code is working on appSync VTL for those special chars:

{
    "version": "2018-05-29",
    "statements": [
        $util.toJson("select * from XXX where YYY like :BodyLike")
    ]
    ,
    "variableMap": {
        ":BodyLike": "%$context.args.BodyLike.replaceAll("\\","\\\\\\\\\\\\\\\\").replaceAll('"','\\"').replaceAll("'","\\\\'").replaceAll("\r","\\\\r").replaceAll("\n","\\\\n").replaceAll("([_%])","\\\\$1")%"
    }
}

OR while concate sql directly

NOTE: the escape for single quote is diff from the above(2 vs 4)

{
    "version": "2018-05-29",
    "statements": [
        $util.toJson("select * from XXX where YYY like '%$context.args.BodyLike.replaceAll("\\","\\\\\\\\\\\\\\\\").replaceAll('"','\\"').replaceAll("'","\\'").replaceAll("\r","\\\\r").replaceAll("\n","\\\\n").replaceAll("([_%])","\\\\$1")%'")
    ]
    ,
    "variableMap": {
        ":BodyLike": "%$context.args.BodyLike.replaceAll("\\","\\\\\\\\\\\\\\\\").replaceAll('"','\\"').replaceAll("'","\\\\'").replaceAll("\r","\\\\r").replaceAll("\n","\\\\n").replaceAll("([_%])","\\\\$1")%"
    }
}

then ,you can use like sql search for those special chars: backslash(\),underscore(_), percentage(%),double quote("),single quote('),and for oracle you can extends [ and ] for your needs.

[ShivamJoker]

3 years gone and yet I can't put single quotes inside string @jpignata you still there?

[petercoppensdatylon]

fwiw, I sort of managed to work around a single quote issue I had when inserting json using something like this

{ "version": "2018-05-29", "statements": [ "select * from somefunction (:SETTINGS)" ], "variableMap": { ":SETTINGS": $util.str.toReplace($util.toJson($ctx.arguments.settings),"'", "''") } }

Feels far from robust tbh, but for lack of something better ....

[herotangabc]

Now, My team is discarding the urgly appsync on our way, and transfering it to apigateway + graphql lambda, more better than appsyn ,good luck ,appsyn teams ,you guys now seems putting out a new arch for appsync,but we have no intrest any more