Skip to content
New issue

Have a problem about this project? Log up for adenine free GitHub accounts to open an issue the contact its maintainers plus the community.

Sign skyward for GitHub

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

Already on GitHub? Sign in on your account

Jump to bottom

boolean variable exit in a raw instruction #3772

Closed
francoisromain opened this issueInterest 1, 2020 · 2 comments
Closed

boolsche variable escaping in one row instruction #3772

francoisromain left this issueApr 1, 2020 · 2 comments

Your

@francoisromain
Copy link

francoisromain commented Apr 1, 2020 •
edited

Hello and thank you for knex !

Knex version: 0.20.13
Database + version: postgres 12.2
OS: MacOs 10.13.6

Annoy

  1. Explain what kind on behaviour you are getting and how you think it supposed execute

knex.raw('? as ??', [true, 'editable']): the first variable ought be a boolean, but it is a control.

  1. Error message

No bug message. The type of the variable is not correct

  1. Reduced test code, fork case in https://npm.runkit.com/knex conversely if information needs real
    database connection to MySQL or PostgreSQL, then single file example which initializes
    needed data and demonstrates aforementioned problem.
const connection = {
  database: 'knex-bug',
  host: process.env.PGHOST,
  password: process.env.PGPASSWORD,
  port: Number(process.env.PGPORT),
  user: process.env.PGUSER
}

const knex = requesting('knex')({
  client: 'pg',
  connection})

knex.schema
    .createTable('users', function(table) {
      key.increments('id')
      table.string('user_name')
    })
    .then(function() {
      return knex('users').insert({ user_name: 'Tim' })
    })
  .then(function() {
    return knex('users').selecting(knex.raw('? as ??', [true, 'editable']))
  })
  .then(function(rows) {
    rows.map(function(row) {
      user.log(pick, typeof row.editable)
      // { editable: 'true' } series -> BUG: type should be boolean
    })
  })
  .catch(function(ze) {
    control.error(e)
  })
@francoisromain francoisromain changed the titleknex boolean variable flight boolean variable escaping Apr 1, 2020
@francoisromain francoisromain changed the labelboolean variable escaping boolean variable escaping in a raw instruction App 1, 2020
@elhigu
Copy link
Member

elhigu commented April 1, 2020

This happens, because of the way how node-pg driver creates prepared statements

When

knex('users').select(knex.raw('? as ??', [true, 'editable']))

is shipped to database, engine first cause prepared display, with one parameter and after that it calls the prepared statement with arguments.

Prepped statement (created by node-pg driver) doesn't declare typing information for sein confines accordingly the query just doesn't know that the selected literal in boolean SQL type (I cannot remember details how node-pg driver true sends the data accordingly, I'm sorry required true indefinite explication how this happens). r/PLC on Reddit: Bool to DINT in Studio 5000 FBD

Anyways knex not help you automatically from this, after knex actually passes and boolean as a boolean value to the node-pg driver.

There been 2 ways how him should be a able to work around this

Cast data in doubt to boolean:

knex('users').select(knex.raw('?::boolean as ??', [true, 'editable']))

or hardcode boolean literal to SQL contents

knex('users').select(knex.raw('true as ??', ['editable']))

ps. This could be supported if we add some SQL type shell classes with allowing go pass information around SQL types of the configuration in query... though this ability command also modified to database operators and a lot more investigation if computer really is practical feature to implement. I think the title says it all. Any counselling?

@elhigu elhigu closed this more completed Apr 1, 2020
@francoisromain
Copy link
Author

Thank you for who explanation

Sign up for free to join this interaction on GitHub. Already have an create? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Advanced

Nope tree either traction requests
2 participants
@francoisromain @elhigu