Do not use sys.tables to find if a table exists

[thomasbiddle]

On AWS RDS (SQL Server Web) access to sys.tables is not available; this query will provide
the same result without relying on the system table.

Tests pass and I'm able to migrate again in my case; but would love for someone else to have a pair of eyes on this; especially due to removing the formattedTable

[elhigu]

@Grimace1975 Could you check if this is ok?

[smorey2]

@thomasbiddle,

I am not sure how any test was passed, unless there was no test exercising this method.

````WHERE TABLE_NAME = ${tableName}`;```

should not function, would need the $(tableName) wrapped as a string.

previously this did such wrapping:
const formattedTable = this.formatter.parameter(this.formatter.wrap(tableName));

Please make sure table is passed accordingly, and that a test is there to check its use.

[thomasbiddle]

@Grimace1975

I'm not sure I understand; when I was testing tableName was passed as a string just fine; and formattedString was always returning a ? in my case.

[smorey2]

The ? Is a variable parameter

…

Sent from my iPhone On Nov 14, 2017, at 2:28 PM, TJ (Thomas J.) Biddle <notifications@github.com<mailto:notifications@github.com>> wrote: @Grimace1975<https://github.com/grimace1975> I'm not sure I understand; when I was testing tableName was passed as a string just fine; and formattedString was always returning a ? in my case. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#2328 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AAvv48NuEg3NvPYxNk7r5sT68LNU4VH_ks5s2fgBgaJpZM4Qd4gt>.

[thomasbiddle]

@Grimace1975

Added in the quotes; must've missed that. Branch reflects this now.

Aware of the ? being a variable parameter; however it looks like the bindings being set are always wrapped in []

Example from some debug output using this PR:

[ { sql: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'migrate\'',
    output: [Function: output],
    bindings: [ '[migrate]' ] } ]

[ { sql: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'migrate_lock\'',
    output: [Function: output],
    bindings: [ '[migrate_lock]' ] } ]

And with substituting formattedTable

[ { sql: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'?\'',
    output: [Function: output],
    bindings: [ '[migrate]' ] } ]

[ { sql: 'if object_id(\'[migrate]\', \'U\') is null CREATE TABLE [migrate] ([id] int identity(1,1) not null primary key, [name] nvarchar(255), [batch] int, [migration_time] datetime)',
    bindings: [] } ]
[ { sql: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'?\'',
    output: [Function: output],
    bindings: [ '[migrate_lock]' ] } ]

When using formattedTable it ends up never finding the table (I've been printing out resp.length to check this.

[smorey2]

The ? Should not have ‘’ around it if I remember correctly. It’s variable parameter with makes execution plan better, but more importantly prevents SQL injection attack vector. I don’t have code in front of me right now. Sure there’s another function that passes a string.

…

Sent from my iPhone On Nov 14, 2017, at 4:09 PM, TJ (Thomas J.) Biddle <notifications@github.com<mailto:notifications@github.com>> wrote: @Grimace1975<https://github.com/grimace1975> Added in the quotes; must've missed that. Branch reflects this now. Aware of the ? being a variable parameter; however it looks like the bindings being set are always wrapped in [] Example from some debug output using this PR: [ { sql: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'migrate\'', output: [Function: output], bindings: [ '[migrate]' ] } ] [ { sql: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'migrate_lock\'', output: [Function: output], bindings: [ '[migrate_lock]' ] } ] And with substituting formattedTable [ { sql: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'?\'', output: [Function: output], bindings: [ '[migrate]' ] } ] [ { sql: 'if object_id(\'[migrate]\', \'U\') is null CREATE TABLE [migrate] ([id] int identity(1,1) not null primary key, [name] nvarchar(255), [batch] int, [migration_time] datetime)', bindings: [] } ] [ { sql: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'?\'', output: [Function: output], bindings: [ '[migrate_lock]' ] } ] When using formattedTable it ends up never finding the table (I've been printing out resp.length to check this. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#2328 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AAvv4zsZnsP30L5PqfexeUSdvyfVDySoks5s2g9-gaJpZM4Qd4gt>.

[kibertoad]

@thomasbiddle Would you like any help with this?

[thomasbiddle]

@kibertoad - That'd be great; I'm not putting any further time into this, just wanted to contribute what I had to get it working for our situation and don't have time to add anything more.

[kibertoad]

Released in 1.0.2