Comments (12)
Actually... I just looked at your most recent commit. Maybe this is an option that I overlooked?
from squel.
No, the autoQuotes option only applies to string field values when INSERT-ing or UPDATE-ing data. Table and field names are still handled as they were before.
To fix this issue we'll need to ensure table and field names are always "escaped" properly. Of course, any table and/or field names which get passed to Squel as part of ON or WHERE clauses will need to be manually escaped by the client as Squel cannot predict what format these clauses will be in.
from squel.
Ah now I remember why Squel doesn't automatically escape them. It's because of the possibility of doing something like the following:
squel.select().from("db1.table1").field("DATE_FORMAT(db1.table1.when, '%Y-%M-%D')", "Start date")
Squel would need to split the above strings at the dot (.) and escape the components such that the output looks like the following:
SELECT DATE_FORMAT(`db1`.`table1`.when, '%Y-%M-%D')" AS "Start date" FROM `db1`.`table1`
It can do this easily for the table name (db1.table1
) but not so easily for the field specification as it cannot predict what format that will be in, and we don't want to be restrictive about what sort of field specifications can be used.
from squel.
Good points. Another example might be a derived table, I suppose. At any rate, I think there should be an option to auto-quote table names and field names. Perhaps, you could have a auto-quote 'on', 'off', and 'guess'? Guess mode (the default) would add quotes to the table name (and field name, if given) only if the table name was of the form /^([a-z]+)(\.[a-z]+)?$/i
What do you think? I'd be willing to do the work over the weekend if you would accept a pull request. :)
Also, IMHO, quoting values is rather useless in Squel because it only adds quotes to the value; it doesn't fully escape the value. This means that your query might be susceptible to SQL injection. And besides, I'd say that the recommended method to avoid SQL injection is to use parameters in all queries and allow node-mysql to do all of the escaping and quoting for you.
from squel.
I totally agree regarding the use of parameterized queries. In fact that's why being able to turn off string quotes was introduced - in order to be able to build parameterized queries for use with node-mysql. Perhaps the autoQuotes
parameter can be replaced with one called useParameters
which has the same behaviour - the name change would be just to make things clearer.
Once that's in we can get squel to automatically quote table and field names where it thinks it can (e.g. using the regex you mentioned). An autoEscape
parameter (default = true) can be set to false by the client if they don't want Squel to do this.
from squel.
I think useParameters
should instead be parameterizedValues
to make its purpose clearer. I'm happy to accept pull requests as long as there are accompanying tests.
Speaking of tests, I might refactor them soon to use vows-bdd as they don't look very elegant at the moment.
from squel.
Ah that might be cool. Do something like:
squel.update({
useParameters: true,
quoteTableNames: true,
quoteFieldNames: true
})
.table("users")
.set("password")
.where("userID=?")
would generate something like:
UPDATE `users` SET `password`=? WHERE `userID`=?
And, for the record, I'd like to revise my regex to include a-z, 0-9, underscore, and dollar sign as per the MySQL Docs. :) Space character might be OK, too, but... I think most sane people substitute with underscore anyway.
from squel.
autoQuotes
is now usingValuePlaceholders
.
from squel.
Cool. I like it.
from squel.
All tests have been rewritten using Mocha + Sinon + Chai. Testing is also more thorough and the test code itself is more maintainable now.
from squel.
Version 1.0.6 has two new options:
autoQuoteTableNames
autoQuoteFieldNames
Note: you will still need to add your own quotes around names inside where clauses, etc.
from squel.
Am considering this done for now.
from squel.
Related Issues (20)
- Add option to add column/table name as a parameter HOT 1
- Add semantic-comparison logic for queries/expressions
- Feature request: Placeholders in the squel.case(...) expression?
- MSSQL Boolean type
- .group().having() is not a function HOT 1
- string escape is incorrect, especially when string contains \n or ', which may cause SQL injection HOT 1
- How to select into array? HOT 1
- Scalar values aliasing
- squel.select().from is undefined after minimizing HOT 6
- Numbered parameters in postgres flavour are substituted inside a string literal
- Incorrect syntax near '`'.
- SQL Server Update Fails with Trigger ? HOT 3
- Use a normal SQL string HOT 2
- Can you get the result value from an object like the result map of mybtis?
- SQL Server Select HOT 1
- Interested in Becoming an Active co-maintainer HOT 1
- How to use IGNORE in INSERT in mysql?
- Mark as deprecated HOT 1
- Grouping conditions together in expression HOT 1
- Lateral Joins
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from squel.