Re: Node.js result set question - Trimming blanks

Ooo.. db.debug(true) helps! :)

Brad
www.bvstools.com

On Sun, Apr 3, 2016 at 2:19 PM, Bradley Stone <bvstone@xxxxxxxxx> wrote:

I'm aware of SQL injection, just testing while I'm getting started.

Have to TRIM() all the values... fun. :)

Now, I tried converting to a prepared statement but it isn't returning
anything in the result set. The documentation is quite sparse so I'm sure
I'm missing something:

var email = req.body.emailSearch.toUpperCase().trim();
var sql = 'select * from cstmstpf where UCASE(CMEMAIL) like ?';

db.init();
db.conn(DBname);
db.prepare(sql);
db.bindParam([
['%' + email + '%', db.SQL_PARAM_INPUT, 0],
]);

try {
db.execute(function callback(out) {
console.log(out);
res.render('customerList', {customerList: out});
});
} catch(e){
console.log('error: ' + e + ' for sql statement ' + sql);
}

when done it should be 'select * from cstmstpf where UCASE(CMEMAIL) like
'STONE'

But results are non-existent. No errors, just nothing returned.

Thanks!

On Sun, Apr 3, 2016 at 11:27 AM, Justin Dearing <zippy1981@xxxxxxxxx>
wrote:

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
--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing
list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/web400.