Go denisenkom/go-mssqldb: mssql: Incorrect syntax near the keyword 'AS'

Question

I am passing the following query string using the github.com/denisenkom/go-mssqldb package:

q := `
SELECT TOP @p1
    firstname,
    lastname,
    username,
    phone,
    email,
    picture,
    bio,
    active,
    joined
FROM (
    SELECT
        t2.first_name AS firstname,
        t2.last_name AS lastname,
        t1.username AS username,
        t2.emp_phone AS phone,
        t2.employee_email AS email,
        t1.picture AS picture,
        t1.bio AS bio,
        CASE WHEN t2.employee_user_id IS NULL THEN 0
        ELSE 1 END AS active,
        t2.date_joined AS joined
    FROM dbo.TBL_BIOS_ANALYSTS AS t1
    LEFT JOIN dbo.TBL_HRMS_STAFF AS t2 
        ON t2.employee_user_id = t1.username
) AS t3  
WHERE lastname=@p2 ORDER BY @p3`

I am passing the following arguments:

args := []interface{}{
    sql.Named("p1", 5),
    sql.Named("p2", "chan"),
    sql.Named("p3", "firstname"), 
}

The query runs fine using a mssql client with a GUI, but using Go and that package, I am getting the following error when doing db.Query(q, args...):

mssql: Incorrect syntax near the keyword 'AS'.

Help pls.

Also, `ORDER BY @p3` will give you an arbitrary order, since it's basically ordering by the constant value of the `@p3` parameter. — Zohar Peled, Jun 11 '19 at 04:47
@ZoharPeled How do I parameterise ORDER BY? The duplicate you referred to doesn't answer that either. — JackyJohnson, Jun 11 '19 at 09:20
Since you can't parameterize identifiers in SQL, your only two options are either to [use case expressions](https://stackoverflow.com/a/13846257/3094533) or [dynamic SQL](https://stackoverflow.com/a/2378754/3094533). With dynamic SQL you should white-list the column name (using sys.columns or information_schema.columns) to prevent the risk of an SQL injection attack. — Zohar Peled, Jun 11 '19 at 09:29
@ZoharPeled Neither of these seem nice... Is it possible to get the column position (as I understand ORDER BY would accept) by parameterising/passing the column name somehow? — JackyJohnson, Jun 11 '19 at 09:42
not in a way that's shorter or nicer than using a case expression or dynamic sql... — Zohar Peled, Jun 11 '19 at 09:44

Go denisenkom/go-mssqldb: mssql: Incorrect syntax near the keyword 'AS'

0 Answers0