Java: Surround injected SQL dynamically

In our project, we have methods that take a WHERE condition as string literal parameter.

 

Using SQL injection directly, this really work because the partial query isn't proper SQL and fails syntax checks and age and name don't resolve to proper columns.

 

    students.where("age > 10 AND name LIKE 'Doe%'");

I would like to create a plugin/language injection extension that provides the context within which the SQL is evaluated.

 

I would imagine something like:

prefix = "SELECT * FROM " + determineTable() + " WHERE";

postfix = ""

Are there any hooks I can use to facilitate this functionality?

 

7 comments
Comment actions Permalink
Official comment

Hi! If I understand correctly what you want then probably `MultiHostRegistrar.addPlace` `suffix` and `prefix` parameters it what you are searching for

Comment actions Permalink

Thanks for the quick reply, I've got some preliminary working stuff using the LivePlugin so far. I will post the code for future reference later.

 

Follow up question: Do I need to change something to get syntax highlighting working in the injected literal? I can do Alt-Enter -> Edit SQL and get an editor with working syntax highlighting and my custom prefix shows up and isn't editable, which is great.

Also, within the injected literal, I get proper completion, symbol navigation etc, but no highlighting.

Follow up question 2: When I'm on some string expression, a combination of concatenation with literals and variables, IntelliJ offers "Inject Language". Then, when using "Edit fragment", the string will show up with "holes" that are greyed out.

Can I also do that with the MultiHostRegistrar? 

0
Comment actions Permalink

1. Highlighting should work out-of-the-box, please check logs for any errors

2. You could do it by calling `addPlace` for each concatenation operand within one `startInjecting`/`donetInjecting` wrap. Implementing `ConcatenationAwareInjector` could help with getting all operands.

1
Comment actions Permalink

1. You were right, I had an NPE in my MultiHostRegistrar

 

2. addPlaces called multiple times

So far, I've used it to get a getAllWhere(Entity.class, "<where>", "<order by columns"); working:

 

Java code:

Why isnt' the "ORDER BY" displayed in the editor? it shows in the PsiViewer. As soon as I *click* on the ", " placeholder, it gets replaced with an uneditable order by and the placeholder disappears

 

the current call sequence is:

addPlace("SELECT * FROM table ", null..., where, range)

addPlace(" ORDER BY ", null..., orderBy, range)

Is this intended behavior?

 

I haven't yet looked into COncatenationAwareInjector much. It seems as if this is itself being used within JavaConcatenationInjectorManager ?

https://upsource.jetbrains.com/idea-ce/file/idea-ce-a5f4151611972df8502464fa96cd0004881bef1d/java/java-impl/src/com/intellij/psi/impl/source/tree/injected/JavaConcatenationInjectorManager.java?nav=3432:3496:focused&line=57&preview=false

Looks like I would also need my own copy of computeAnchorsAndOperands ? 

https://upsource.jetbrains.com/idea-ce/file/idea-ce-a5f4151611972df8502464fa96cd0004881bef1d/java/java-impl/src/com/intellij/psi/impl/source/tree/injected/JavaConcatenationInjectorManager.java?nav=7373:7397:focused&line=210&preview=false

 

I see ConcatenationAwareInjectors can be registered via an extension point, would I then need to determine the "context" (i.e. whether I'm in the method call I'm interested in) manually?

0
Comment actions Permalink

For future reference, here's the code that I'm using in combination with LivePlugin to facilitate the dynamic SQL injections:

 

import com.intellij.ExtensionPoints
import com.intellij.lang.injection.MultiHostInjector
import com.intellij.lang.injection.MultiHostRegistrar
import com.intellij.openapi.extensions.ExtensionPoint
import com.intellij.openapi.extensions.ExtensionPointName
import com.intellij.openapi.extensions.Extensions
import com.intellij.openapi.util.TextRange
import com.intellij.psi.PsiClass
import com.intellij.psi.PsiClassObjectAccessExpression
import com.intellij.psi.PsiElement
import com.intellij.psi.PsiExpression
import com.intellij.psi.PsiField
import com.intellij.psi.PsiJavaCodeReferenceElement
import com.intellij.psi.PsiLanguageInjectionHost
import com.intellij.psi.PsiLiteral
import com.intellij.psi.PsiLiteralExpression
import com.intellij.psi.PsiMethod
import com.intellij.psi.PsiMethodCallExpression
import com.intellij.psi.impl.JavaConstantExpressionEvaluator
import com.intellij.psi.impl.source.tree.java.PsiLiteralExpressionImpl
import com.intellij.psi.impl.source.tree.java.PsiMethodCallExpressionImpl
import com.intellij.psi.util.PsiTreeUtil
import com.intellij.psi.util.PsiUtil
import com.intellij.sql.psi.SqlLanguage
import com.intellij.util.text.TextRanges
import groovy.transform.Canonical
import org.codehaus.groovy.ast.expr.MethodCallExpression
import org.jetbrains.annotations.NotNull

import static liveplugin.PluginUtil.*

def patterns = [
new Pattern("getAllWhere", ["Class<T>", "String", "SystemInfo", "Connection"], { pattern, registrar, psiMethod, methodCallExpression ->

String table = getTableName(methodCallExpression.argumentList.expressions[0])
if (table == null) return

List<InjectionPlace> wheres = getPlaces(methodCallExpression.argumentList.expressions[1])
if (wheres.size() == 0) return

boolean isFirst = true;

def select = "SELECT * FROM " + table + " WHERE "
registrar.startInjecting(SqlLanguage.INSTANCE)
for (InjectionPlace where : wheres) {
String prefix = mergePrefixWithFirst(isFirst, select, where)
registrar.addPlace(prefix, where.suffix, where.place, where.textRange)
isFirst = false;
}

registrar.doneInjecting()
}),

new Pattern("getAllWhere", ["Class<T>", "String", "String", "SystemInfo", "Connection"], { pattern, registrar, psiMethod, methodCallExpression ->
def classArgument = methodCallExpression.argumentList.expressions[0];

if (classArgument instanceof PsiClassObjectAccessExpression) {
String table = getTableName(classArgument)
if (table == null) return

List<InjectionPlace> wheres = getPlaces(methodCallExpression.argumentList.expressions[1])
List<InjectionPlace> orderBys = getPlaces(methodCallExpression.argumentList.expressions[2])
if (wheres.size() + orderBys.size() == 0) return

boolean isFirst = true;

def select = "SELECT * FROM " + table + " WHERE "
registrar.startInjecting(SqlLanguage.INSTANCE)
for (InjectionPlace where : wheres) {
String prefix = mergePrefixWithFirst(isFirst, select, where)
registrar.addPlace(prefix, where.suffix, where.place, where.textRange)
isFirst = false;
}

String orderByPrefix
if (isFirst) {
orderByPrefix = "SELECT * FROM " + table + " ORDER BY "
} else {
orderByPrefix = " ORDER BY "
}
isFirst = true

for (InjectionPlace orderBy : orderBys) {
String prefix = mergePrefixWithFirst(isFirst, orderByPrefix, orderBy)
show("Order by prefix: " + prefix)
registrar.addPlace(prefix, orderBy.suffix, orderBy.place, orderBy.textRange)
isFirst = false;
}
registrar.doneInjecting()
} else {
show("First paramet is not a class argument")
show(classArgument)
}
})
]


@Canonical
class MyCustomMultiHostInjector implements MultiHostInjector {
List<Pattern> patterns;

@Override
void getLanguagesToInject(@NotNull MultiHostRegistrar registrar, @NotNull PsiElement context) {
if (context instanceof PsiMethodCallExpression) {
PsiMethodCallExpression methodExpression = context
def resolvedMethod = methodExpression.resolveMethod()
if (resolvedMethod == null) {
// Not sure when this happens, but we better guard against it
// Maybe thjis happens when we don't have these classes indexed/on the classpath??
// show("Method was null, but we're inside a methodExpression")
// show(methodExpression.text)
// show(methodExpression.containingFile.name)
// 13:56 ButtonDetector.java
//13:56 Method was null, but we're inside a methodExpression
//13:56 MethodSource.from("className", "methodName")
//13:56 JUnit5NavigationTest.java

return
}
def name = resolvedMethod.name
// TODO: Doesn't mean we're directly inside a method call, could be nested anywhere...
if (name == null) {
return
}

patterns.forEach({ pattern ->
if (name.equals(pattern.methodName)) {
// show("Found getAllWhere method")
// show("Parameters " + resolvedMethod.parameters)
// show("parameterList" + resolvedMethod.parameterList)
// show("Location" + resolvedMethod.textRange)
// show("Location" + resolvedMethod.containingFile)

def actualSignature = resolvedMethod.parameterList.parameters*.typeElement*.text.toString()
def expectedSignature = pattern.parameterTypes.toString()
// show("Actual signature: " + actualSignature)
// show("Expected signature: " + expectedSignature)
if (actualSignature == expectedSignature) {
pattern.applyRegistrar.apply(pattern, registrar, resolvedMethod, methodExpression)
}
}
})
}
}

@Override
List<? extends Class<? extends PsiElement>> elementsToInjectIn() {
return Collections.singletonList(PsiMethodCallExpression.class)
}
}

@Canonical
class Pattern {
String methodName
List<String> parameterTypes
ApplyRegistrar applyRegistrar
}

interface ApplyRegistrar {
void apply(Pattern pattern, MultiHostRegistrar registrar, PsiMethod psiMethod, PsiMethodCallExpression methodCallExpression)
}

@Canonical
class InjectionPlace {
PsiLiteralExpression place
TextRange textRange
String prefix
String suffix
}


String getTableName(PsiExpression expression) {
// dynamically determine the table name
// by convention, our project has a static TABLE = "tableName" field on some class or super class

if (expression instanceof PsiClassObjectAccessExpression) {
PsiClassObjectAccessExpression objectAccessExpression = expression
def innerMostElement = objectAccessExpression.operand.innermostComponentReferenceElement
def resolved = innerMostElement.resolve()
if (resolved instanceof PsiClass) {
PsiClass psiClass = resolved
def found = psiClass.allFields.find { it.name.equals("TABLE") }
if (found != null && found.initializer instanceof PsiLiteralExpression) {
PsiLiteralExpression initializerExpression = found.initializer
def constantInitializer = JavaConstantExpressionEvaluator.computeConstantExpression(initializerExpression, true)
if (constantInitializer instanceof String) {
return constantInitializer
}
}

} else {
// show("Not a PsiClass")
// show(innerMostElement)
// show(innerMostElement.class)
}
}

return null;
}

List<InjectionPlace> getPlaces(PsiExpression expression) {
List<InjectionPlace> result = []
if (expression instanceof PsiLiteralExpressionImpl) {
result.add(new InjectionPlace(expression, new TextRange(1, expression.textLength - 1), null, null))
}

return result
}

String mergePrefixWithFirst(boolean isFirst, String prefix, InjectionPlace injectionPlace) {
if (isFirst) {
if (prefix == null) {
return injectionPlace.prefix
}

if (injectionPlace.prefix == null) {
return prefix
}

return prefix + injectionPlace.prefix
} else {
return injectionPlace.prefix
}
}

String mergeSuffixWithFirst(boolean isFirst, String suffix, InjectionPlace injectionPlace) {
if (isFirst) {
if (suffix == null) {
return injectionPlace.suffix
}

if (injectionPlace.suffix == null) {
return suffix
}

return suffix + injectionPlace.suffix
} else {
return injectionPlace.suffix
}
}


// Dynamically register MultiHostRegister at runtime
def pointName = MultiHostInjector.MULTIHOST_INJECTOR_EP_NAME
def point = Extensions.getArea(currentProjectInFrame()).getExtensionPoint(pointName)
def length = point.extensions.length
// Try to clean up previous version by comparing the name of all registered injectors against our class name
for (int i = 0; i < length; i++) {
def injector = point.extensions[i]
if (injector.class.name.contains(MyCustomMultiHostInjector.class.simpleName)) {
point.unregisterExtension(injector)
show("Clean up done")
break
}
}
point.registerExtension(new MyCustomMultiHostInjector(patterns))
0
Comment actions Permalink

> Why isnt' the "ORDER BY" displayed in the editor?

> Is this intended behavior?

Yes, it is intended behaviour, in the fragment-editor all suffixes replaced by a placeholder with text taken from original file text between injected parts. And moreover, in IDEA 2018.3 placeholders will become non-unfoldable. Please roll an issue in youtrack if you want more control over it.

> Looks like I would also need my own copy of computeAnchorsAndOperands ? 

You need it if you want to keep using MultihostInjector but to get access to other operands. Otherwise If you implement ConcatenationAwareInjector instead of MultihostInjector you will get all operands instead of one and you can work with them same way you did it with one.

> I see ConcatenationAwareInjectors can be registered via an extension point, would I then need to determine the "context" (i.e. whether I'm in the method call I'm interested in) manually?

yes, you could do it by walking PSI tree

 

0
Comment actions Permalink

Thanks again for the reply.

I don't really mind if it's non-unfoldable. As a user, it would not make sense to me, however, that I could see the prefix but not parts in between.

0

Please sign in to leave a comment.