Re: Node.js result set question - Trimming blanks

On Sun, Apr 3, 2016 at 11:16 AM Bradley Stone <bvstone@xxxxxxxxx> wrote:

var sql = 'select CMEMAIL, CMLNAME, CMFNAME from bvscomp.cstmstpf where
UCASE(CMEMAIL) like UCASE(\'%' + email + '%\')';

<snip/>

Now, I can't figure out what isn't (or if it should be) "trimming" the
blanks off the end of the each result. Is that a setting in the SQL query,
or in jade?

Nothing SHOULD be trimming it. I would trim in SQL like so (untested):

var sql = "select TRIM(CMEMAIL) AS CMEMAIL, TRIM(CMLNAME) AS CMLNAME,
TRIM(CMFNAME) AS CMFNAME from bvscomp.cstmstpf where
UCASE(TRIM(CMEMAIL)) = '" + email.trim() + "'";

The next level would be to encapsulate all those TRIMs into a view (see
CREATE VIEW) and create an index on the trimmed CMEMAIL column.

Note I take advantage of javascripts ability to use " as well as ' to
denote string literals so I don''t have to escape the SQL string literal
inside the javscript string literal.

One final thing. *NEVER* concatenate untrusted input from the user ( the
email address) into a SQL string. It presents a surface area for SQL
injection. Always prepare/execute like on this page:
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20access%20APIs