Best Practices for hardening field names in ExecuteSQL

Questions
,
3

The following is part of the Typinator set that I got from Matt Petrowsky or filemakerstandards.org:

Let ( [ ~sql = "
	SELECT t1.~field
	FROM ~table1 t1
	JOIN ~table2 t2
	ON t1.~field = t2.~field
	WHERE ~field LIKE '%~value%'
	AND ~field=?
	ORDER BY ~field";

	$sqlQuery = Substitute ( ~sql ;
		[ "~table1" ; SQLTableName ( Table1::fieldName ) ];
		[ "~table2" ; SQLTableName ( Table2::fieldName ) ];
		[ "~field" ; SQLFieldName ( Table1::fieldName ) ];
		[ "~value" ; Table::field ]
	);

	$sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" ;
    	$value;
    	$value[2];
    	$value[$n]
	)
];
	//Substitute ( $sqlQuery ; "	" ; "" ) &ΒΆ& // sql preview
	If ( $sqlResult = "?" ;
		Let ( ~debug = False ; If ( ~debug ; SQLDebugResult ( $sqlResult ) ; False ) );
		$sqlResult
	)
)

The Custom Functions SQLTableName and SQLFieldName get the table name part or the field name part of a full field name.
A great use case for text expansion tools like Typinator.

2 Likes