Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Parameter is not working with sub-request #200

Open
mbarnach opened this issue Feb 5, 2021 · 0 comments
Open

Parameter is not working with sub-request #200

mbarnach opened this issue Feb 5, 2021 · 0 comments

Comments

@mbarnach
Copy link
Member

mbarnach commented Feb 5, 2021

When using the Parameter type to avoid SQL injection, the queryBuilder is not working with sub-request, restarting the counter to 1 each time.

If building the following query (from the README tables)

let query = Select(from: grades)
    .where(
        courses.name == Parameter() &&
        courses.credit == Parameter() &&
        courses.teacher.in(
        Select(courses.teacher, from: courses)
            .where(courses.teacher == Parameter() )))

We get with PostgreSQL the following SQL request:

SELECT * FROM "Grades" WHERE (("Courses"."name" = $1) AND ("Courses"."credit" = $2)) AND ("Courses"."teacher" IN (SELECT "Courses"."teacher" FROM "Courses" WHERE "Courses"."teacher" = $1))

Notice that in the last part of the request, teacher is assigned the value $1 instead of $3.
Or name is assigned the value $1 instead of $3 (depending on the order that should make sense here.
The request cannot be run if we provide 3 arguments, as this is checked before.

As far as I can tell, this is coming from the fact that requests are resolved independently, hence the reset of the index.

For reference, MySQL is not suffering from the same issue, as it doesn't use numbered parameters. The same request returns:

SELECT * FROM `Grades` WHERE ((`Courses`.`name` = ?) AND (`Courses`.`credit` = ?)) AND (`Courses`.`teacher` IN (SELECT `Courses`.`teacher` FROM `Courses` WHERE `Courses`.`teacher` = ?))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant