phonega5sql

[Doc Jump]

I have had a difficult time getting the phonegap sql db operations to work properly, so I am trying to employe html5sql. Everything seems to work except for inserting variable values into a table. This is the code I'm testing with:

html5sql.process(
            [
    'CREATE TABLE IF NOT EXISTS idData(id INTEGER PRIMARY KEY AUTOINCREMENT,firstName VARCHAR(50),lastName VARCHAR(50),address VARCHAR(50),license VARCHAR(50),age VARCHAR(10),city VARCHAR(50),dob VARCHAR(8), exp VARCHAR(8),idDate VARCHAR(20),purchase VARCHAR(50));',
    'INSERT INTO idData(firstName,lastName,address,license,age,city,dob,exp,idDate,purchase) VALUES  ("'+gfname+'" , "'+glname+'","'+gaddress+'","'+glicense+'","'+gage+'","'+gcsz+'","'+gdob+'","'+gexp+'","'+myDate+'","'+gpurchase+'");',
    'INSERT INTO idData(firstName,lastName,address,license,age,city,dob,exp,idDate,purchase) VALUES  (?,?,?,?,?,?,?,?,?,?}',[gfname,glname,gaddress,glicense,gage,gcsz,gdob,gexp,myDate,gpurchase]);
   "INSERT INTO idData(firstName,lastName,address,license,age,city,dob,exp,idDate,purchase) VALUES  ('George','Bernard','983 Third St.','H0614345','48','San Diego, CA 90023','19661222','20150712','20100214090900','Indigo 1oz.');",
      ],
            querySuccess,
            errorCB
        );
According to what little documentation there is on the subject, the first two INSERTs should work fine but simply do not. They do nothing in the code except refuse to execute the remainder of code in the function where this code resides. The third INSERT which stores constant values, not variable values, works fine. I'm hoping to that one of you will have gained some html5sql knowledge in your phonegap travels and be able to shed some light on my problem.. Thanks

[Kerri Shotts]

I'm assuming you're using this? http://html5sql.com/guide.html

If so, read the guide more closely -- when using bind parameters (the ?'s), apparently you have to change the format to:

[    

    ...,

    {

        sql: "insert into a values (?,?,?)",

        data: ["one", "two", "three"]

    }, ...

]

Just so you know, you should never, ever concatenate data into your SQL commands -- this is a huge security risk. Untrusted data can change the meaning of the command and retrieve, change, and delete data. As to why it didn't work, I suspect it's the double quotes you're surrounding the data with (assuming I'm reading that right) -- "abc" in SQL is an identifier, not data, whereas 'abc' is a string value and not an identifier.

I've had no direct experience with html5sql, so if you need more assistance, it might just be better to switch to one of the several Cordova SQLite plugins (see plugins.cordova.io). The JS syntax is a little more difficult, but not by much, and you're apt to get better help (since a lot of us on the forums use them). This (cordova-sqlite-ext) is the one I've used most recently (because I needed pre-populated databases).

Hope that helps.

On Tuesday, June 28, 2016 at 4:32:37 PM UTC-5, Doc Jump wrote:

...

[Doc Jump]

Hello Kerri. I am more than pleased that someone has replied to my post. I have never had much success in getting answers here, but I didn't know where else to go. So here is a big THANK YOU to you!! However, yes I am familiar with the web page you referenced. In fact, I have spent more time than I care to admit staring at it. It does not go into the details of the INSERT statement and doesn't even mention the UPDATE statement which I need to use. You mentioned something about my syntax "'+glname+'" etc. but I found thru some other sites that this is appropriate for declaring variables to be stored in a db when not using the "?"s.
When you talk about concatenation, are you referring to the fact that I tried to store all variable values in one statement to be inserted??
The real kicker here is that the same code I showed herein works in another program. That is why I have been clinging to it for so long, but I will try your suggestion. Again, thanks so much!
Doc

On Tuesday, June 28, 2016 at 2:32:37 PM UTC-7, Doc Jump wrote:

I have had a difficult time getting the phonegap sql db operations to work properly, so I am trying to employ html5sql. Everything seems to work except for inserting variable values into a table. This is the code I'm testing with:

[Kerri Shotts]

See inline:

On Tuesday, June 28, 2016 at 10:27:22 PM UTC-5, Doc Jump wrote:
> Hello Kerri. I am more than pleased that someone has replied to my post. I have never had
> much success in getting answers here, but I didn't know where else to go. So here is a big
> THANK YOU to you!!
> However, yes I am familiar with the web page you referenced. In fact, I have spent more time
> than I care to admit staring at it. It does not go into the details of the INSERT statement and
> doesn't even mention the UPDATE statement which I need to use.

The library, as I see it, is intended as a wrapper around the Web SQL standard. The only implementation of that standard used SQLite, which is also what the third-party plugins for Cordova also use. Given that Web SQL itself is deprecated, I'd suggest switching to one of the third-party plugins, but I suspect that the html5sql library would keel over if you did so.

For help regarding SQL as understood by SQLite, see https://www.sqlite.org/lang.html

An UPDATE SQL statement takes the form:

UPDATE table
SET field = ? [, ...]
[WHERE condition]

> You mentioned something about my syntax "'+glname+'" etc. but I found thru some other sites
> that this is appropriate for declaring variables to be stored in a db when not using the "?"s.

I don't know which sites gave you this information, but this is /never/, //ever// appropriate when using variables, because this practically guarantees that your app is subject to SQL Injection attacks. The reason is that any untrusted data could be built to escape the quotes and then execute additional queries, or even corrupt and delete data. (Furthermore, even if your variables contain only trusted data, you still have the problem of some data containing a quote that then makes that SQL statement invalid.)

For more on SQL Injection, read: https://www.owasp.org/index.php/SQL_Injection

In short, when you need to pass data to a SQL command, always, always, always, always use parameters ("?").

> When you talk about concatenation, are you referring to the fact that I tried to store all variable
> values in one statement to be inserted??

In this instance, concatenation refers to using "+" to build a SQL string. You should never mix SQL and untrusted data in this manner.

> The real kicker here is that the same code I showed herein works in another program. That is
> why I have been clinging to it for so long, but I will try your suggestion. Again, thanks so much!

Perhaps you had an older version of the html5sql library that behaved differently? Or there's additional processing going on that isn't evident from the documentation. Without seeing the code you used in the other program, it's difficult to say why. Since I'm not familiar with the library you're using, I'm just guessing as to why it might be failing -- and I may be completely wrong in that respect.

Personally, I'd dispense with this library and use one of the Cordova SQLite plugins (and syntax) directly. It's not much more difficult, and you won't have to fuss with a library that few people are going to be able to help with. But that's my two cents. :-) This [https://github.com/litehelpers/cordova-sqlite-ext] is the plugin that I use.

Hope that helps!

[Rob Willett]

Kerri is correct regarding the use of parameter bindings in SQLite. 

The issue you have is that somebody could put an odd string into a variable field which then gets executed by SQLite. I see this all the time on my website where people try to create new users with odd names such as "(delete from XXXX)" or similar. Its surprising how many attempts I get to hack into my site :)

An excellent site to read regarding SQL injection attacks is here

http://www.unixwiz.net/techtips/sql-injection.html

This site is based on real world experiences 

as well as the obligatory XKCD cartoon

https://xkcd.com/327/

Rather surprisingly we don't explicitly use SQLIte in our app, though we are heavy users of SQLite at the backend. Our largest SQLite database is 32GB and is fine. I know of DB's of 100's of GB's using SQLite.

I had a quick look at http://html5sql.com/guide.html# and it looks OK, but the lack of support forum rang alarm bells for me. The website looks OK and it appears that parameterised bindings using '?" appears to be supported. There doesn't appear to be much else in the way of language support, but since we don't use SQLite in the client, may be that simple JavaScript wrappers are the norm. 

We also looked at https://github.com/litehelpers/cordova-sqlite-ext and thought that looked a little more professional and up to date. The author appears to have documented a lot of things that don't work or have issues. This would give me the confidence that they authors have been around the block and understand their code. Personally speaking we'd look at https://github.com/litehelpers/cordova-sqlite-ext first. 

Once you have the code working, you will need to really think about the SQL design. To be honest you should be thinking about it now before you start as the design of the tables, and the indexes is crucial to efficiency. Simply putting an index on every possible field is NOT the answer, you need to think very carefully about the data, how you start it, how you insert it, are duplicates allowed, how you access it and how you delete it. Doing this as you go along will be a recipe for disaster unless your data is only a few hundred rows of a table. Its trivial to get a SQLite database going on any box at all, what you get right on your desktop PC will work on your phone, so design it locally and then deploy it to your device. We design and build on Mac's, export the schema and any necessary data to a Linux box and do the heavy lifting there. 

If you want to use Sqlite properly join the SQlite mail group. They are excellent and will help with Sqlite specific queries, they will NOT help with your Javascript/Sqlite issues but they are a gold mine of information on how to make your DB sing.

Rob

--
-- You received this message because you are subscribed to the Google
Groups "phonegap" group.
To post to this group, send email to phon...@googlegroups.com
To unsubscribe from this group, send email to
phonegap+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/phonegap?hl=en?hl=en

For more info on PhoneGap or to download the code go to www.phonegap.com
---
You received this message because you are subscribed to the Google Groups "phonegap" group.
To unsubscribe from this group and stop receiving emails from it, send an email to phonegap+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Doc Jump]

Thank you both very much for your assistance. I will try sqlite. Can either of you recommend a good (and simple) javascript editor/compiles individual files for errors, etc.)? Thanks again.

On Tuesday, June 28, 2016 at 2:32:37 PM UTC-7, Doc Jump wrote:

[Rob Willett]

Javascript is an interpreted language, though we can argue over how the interpreter works.

This means unless you move to TypeScript or similar, you won't get any errors until you run your program and watch it throw an error.

Now my personal view is that going to TypeScript or similar from the beginning is a good thing but it's another level of complexity to understand and manage. There are loads of advantages of TypeScript but are you prepared to learn what typescript can do? Google TypeScript for more information.

Editors are numerous. You can edit with Notepad upwards. There are recent dedicated editors such as Brackets, Atom etc.

However there is only one true editor and that's Emacs. A fantastic operating system with a decent editor as well :) Emacs may be slightly (?) complicated but for compilation and managing large projects it is brilliant. YMMV.

Rob

--

[Doc Jump]

Thanks Rob. Running the program and looking for errors or using confirm statements is a slow and requires guesswork. I used to employ Eclipse, but I desinged this project with the cordova cli which isn't compatible with exclipse . I have also occasionally WebStorm. It will show syntax errors but you must scroll all the way thru the program to find them. I suppose the worst problem is when I make a change to the program and inadvertently make a syntax error causing to the program to yield only a blank screen. That is very difficult to find. Thanks again for your assistance.

On Tuesday, June 28, 2016 at 2:32:37 PM UTC-7, Doc Jump wrote:

[Kerri Shotts]

I'm out and about so I can't say a whole lot, but I would suggest linting as a build step and Visual Studio Code as an editor. :)

--

-- You received this message because you are subscribed to the Google
Groups "phonegap" group.
To post to this group, send email to phon...@googlegroups.com
To unsubscribe from this group, send email to
phonegap+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/phonegap?hl=en?hl=en
 
For more info on PhoneGap or to download the code go to www.phonegap.com
---

You received this message because you are subscribed to a topic in the Google Groups "phonegap" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/phonegap/agD1o3TJJK0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to phonegap+u...@googlegroups.com.

[Rob Willett]

All our work here is command line, we don't have any integrated development environments like Eclipse. If we can't find something that does what we want, we'll write it ourselves, though we are doing that less and less. 

We run old-school Emacs combined with command line build tools, Perl, SQL, Makefiles etc. People laugh and start talking about WebStorm, Xcode etc etc until they see how fast our workflow is. There's a reason why Emacs and stuff like this is still being used. If you know it, it works well. We use modern tools as well, but we're very much command line orientated as we appreciate the speed. We all know how to write bash scripts, Perl scripts, Regexp stuff.

We moved our entire project from JavaScript to TypeScript over three weeks, though the majority of the work was done in two 8hr stints. We have 20,000 lines of code, though comments account for a fair chunk. We had to write a wrapper for TypeScript so the errors are put into a file format suitable for Emacs, as TypeScript appears to be Microsoft they thought they'd write their output in their format. A Perl script put that right.

macpro:ts rwillett$ wc -l *.ts

     395 app.ts

    1813 controllers.ts

     460 controllers_detailctrl.ts

      54 controllers_filters.ts

      70 controllers_help.ts

      59 controllers_intro.ts

     102 controllers_jamcamctrl.ts

      50 controllers_minor.ts

     190 controllers_navctrl.ts

     146 controllers_restorelistctrl.ts

     428 controllers_routedetailctrl.ts

     475 controllers_routesctrl.ts

     306 controllers_settings.ts

    1471 controllers_summaryctrl.ts

      75 globals.ts

     229 interfaces.d.ts

    1859 perimeter.ts

     486 polygon.ts

     560 services.ts

      83 services_analyticsdata.ts

     531 services_averagespeedzone.ts

      63 services_battery.ts

     152 services_cams.ts

     121 services_cloudmanagement.ts

     610 services_congestionzone.ts

     147 services_copyrights.ts

     228 services_defaults.ts

     279 services_disruptiondata.ts

    1652 services_gps.ts

     250 services_help.ts

     497 services_history.ts

     118 services_iconcache.ts

     219 services_intro.ts

     118 services_keychain.ts

     113 services_layers.ts

     222 services_loaderservice.ts

     267 services_namedtimers.ts

      74 services_notificationpoller.ts

    1687 services_notifications.ts

     244 services_pcn.ts

     118 services_poller.ts

     164 services_redroute.ts

    1255 services_route.ts

     150 services_uuid.ts

     185 test_journeys.ts

     601 urls.ts

     955 utilities.ts

   20331 total

My advice is to go with TypeScript. I think the MS Code Editors support it but don't really know. It will speed your development up when you miss out a little comma and your JavaScript won't work, you then have to fire up a debugger to catch something trivial. We catch a lot of simple typing errors at compile time. Its well worth the effort.

Rob

--

-- You received this message because you are subscribed to the Google
Groups "phonegap" group.
To post to this group, send email to phon...@googlegroups.com
To unsubscribe from this group, send email to
phonegap+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/phonegap?hl=en?hl=en
 
For more info on PhoneGap or to download the code go to www.phonegap.com
---

You received this message because you are subscribed to the Google Groups "phonegap" group.

To unsubscribe from this group and stop receiving emails from it, send an email to phonegap+u...@googlegroups.com.

[Kerri Shotts]

My expanded suggestions:

• Get in the habit of creating build automation scripts, because anything more than simple JavaScript is going to require it anyway.
• There are lots of options: Gulp, Grunt, npm scripts, shell scripts, etc. It doesn't matter which you use -- just build some automation.
• In your build automation scripts:
• Lint your code. I use "eslint" for ES2015 code -- there are TypeScript linters as well.
• I like eslint because it is extensible and does more than simple syntax checking. For example, it can check code complexity and throw errors when functions become too complex.
• Check your code style. I use "jscs" for this. (Not sure if there is a TypeScript version?) 
• This won't necessarily catch syntax errors, but it will help you enforce your coding style, if you have one.
• Compile your code (if necessary). This is a requirement if using TypeScript or Babel (for ES2015). In Babel's case, syntax errors will be caught this way. When using TypeScript, type errors will be caught as well.
• Get in the habit of running your build steps before you run your code.
• If using Babel/TypeScript, you have to do so anyway, but when using vanilla JavaScript, it's easy to ignore. Run the build steps anyway to catch syntax errors and save yourself a lot of headaches!
• Better yet, have your build steps watching your code so that they can run automatically whenever you save changes.
• Editors:
• You can edit JavaScript in any environment you like. I've done so in just about every editor out there, I think!
• Some environments are "nicer" than others, obviously. If you're a pro at emacs, there are a crazy amount of plugins that make emacs and JavaScript play nice together. The same applies to VIM.
• Visual Studio Code and TypeScript go together like Peanut Butter and Jelly. VSC uses the underlying TypeScript engine to also provide intellisense for non-TypeScript code, so it can be used for vanilla JavaScript as well. Although VSC is an electron-based app, it's very fast at what it does, and has a lot of automation built in for Cordova apps, task runners (gulp, grunt, etc.), compilation steps (for TypeScript especially), etc. The debugger is excellent as well.
• Web Storm is good, but I think VSC beats it, especially in terms of performance. But WS is much more an IDE than it is an editor, and so if one likes IDEs, WS is not at all a bad choice.
• Atom / Brackets / Sublime / etc can all be turned into excellent JavaScript editors / IDEs. 
• Regardless, if you run a build step between each run from the CLI, your choice of editor really does not matter much. Use whichever editor in which you are most productive. All the editors mentioned can note errors in your code, though some will only do so at save time, and some can only do so with plugins. Don't rely on the editor noting your error though -- that's what your build step is for.
• Side note: my current setup is a mix between VSC and VIM. I switch between the two frequently simply based on what I'm doing. Fast JS edit? VIM. Lots of work within a specific project? VSC. Still waiting for VSC to have some decent VIM key-bindings though. :-(
• TypeScript / ES2015 / Vanilla JavaScript (ES5)
• TypeScript is awesome. Use it if you can. This will catch out a lot of type errors which are rather insidious in JavaScript.
• ES2015 is awesome. Use it if you can. A lot of the syntax has made its way into TypeScript, so you get the best of both worlds -- modern syntax AND type checking. If using Babel, syntax errors are caught at compile time. Types are entirely up to you to manage, though.
• ES5 is still pretty good (much better than ES3!). Linting will help catch out syntax errors at build time. Types are entirely up to you to manage. Syntax is a bit more ugly. Keep in mind that both TS and ES2015 will compile into ES5 code, so compatibility isn't really an issue here (as in, don't ignore TS/ES2015 just because a browser may only support ES5).

[Steve Husting]

Wow, what a gold mine of information!!

[Doc Jump]

Thank you all for your extensive and informative comments. Doc

On Tuesday, June 28, 2016 at 2:32:37 PM UTC-7, Doc Jump wrote: