Says there is no table, but there is

[Michael_Cebasek]

Hi there.

This is strange. This line, works in another program, but not in one I am writing now.

addAPasswordSQLString = "INSERT INTO passwords (siteName, userName, sitePassword) VALUES ('" +addPasswordSiteTextField.text + "','"+addPasswordUsernameTextField.text+"','"+addPasswordSitePasswordTextField.text+"')"

I can connect to the database, find the table, select the table, see what is in the table, but when I try to run this command in a method, it still says

Database exception ; no such table: passwords.

Can anyone see what is wrong? I can’t.

Regards

[AlbertoD]

In the past I have seen people with this problem, their program is actually connecting to a different database.

Can you check if you have another database with the same name in your system without the passwords table?

I don’t think passwords is a reserved word (that could cause problems if it is).

[Kem_Tekinay]

Once you get it working, try inserting this password:

hi'); drop table passwords; --

Actually, don’t do that. Lookup “SQL injection” and then switch to Prepared Statements to avoid those kinds of consequences.

(Also don’t store plain text passwords.)

[Tim_Hare]

Examine your sql string in the debugger. It may be malformed. Then switch to prepared statements. They’re safer and easier.

[TimStreater]

@Michael_Cebasek

addAPasswordSQLString = "INSERT INTO passwords (siteName, userName, sitePassword) VALUES ('" +addPasswordSiteTextField.text + "','"+addPasswordUsernameTextField.text+"','"+addPasswordSitePasswordTextField.text+"')"

Much easier to do this which uses the built-in prepared statement feature:

sql = "INSERT INTO passwords (siteName, userName, sitePassword) VALUES (?1, ?2, ?3)"
db.ExecuteSQL (sql, addPasswordSiteTextField.text, addPasswordUsernameTextField.text, addPasswordSitePasswordTextField.text)

.
It’s much more legible and much less chance of screwing up the string with your fifty billion quote marks.

Moderator Edit: Inserted missing end quote.

[Ulrich_Bogun]

… and, still talking security, you should consider storing just the hash of a password instead of the real string, especially if your database has connection to the world outside a single computer (and if it’s meant for log-ins. Not suitable if you are programming a password manager for yourself – where some encryption still would be a good idea.)

[Michael_Cebasek]

@TimStreater

(sql, addPasswordSiteTextField.text, addPasswordUsernameTextField.text, addPasswordSitePasswordTextField.text)

Oh, I NEVER store passwords in plain text. EVER.

[Michael_Cebasek]

Time for me to read up on those prepared statements. I’m and old dog who hasn’t learned new tricks yet.

But now that I say that, and this is something that will probably make me the laughing stock here, but I’ll ask anyway.

You create things like
thedb as New SQLiteDatabase

And then have things like
thedbFile as FolderItem

Why not just have one thing?

Stupid question I know (but everytime I think I have it figured out, I realize I don’t)

Regards

[Tim_Hare]

There’s a group of things that are database connections. SQLiteDatabase is the only one of them that uses a FolderItem, thus the separation. That said, you don’t have to create a variable for it, you can use thedb.DatabaseFile directly. It’s just a matter of personal preference at that point.

I’d just like to point out you will still see references to PreparedStatement classes in the documentation. They are a little convoluted, but are no longer necessary. SelectSQL and ExecuteSQL incorporate prepared statements and make them a lot easier.

[Michael_Cebasek]

You can use the (databaseName).DatabaseFile directory without declaring a new, for example, SQLite database first?

How?

If I don’t do it, my program won’t run.

Regards

[Tim_Hare]

No, you do have to create a new database object. What I meant was you don’t need a separate variable thedbFile. You can use thedb.DatabaseFile instead of declaring a separate thedbFile variable. It was in response to your question “Why not just have one thing?” Perhaps I misunderstood what you were asking.

[TimStreater]

Well your original code as posted in your OP, was. All I did was recast it to make it more readable and to use the automatic prepared statement feature of ExecuteSQL.

I wouldn’t bother reading up on prepared statements unless your needs for them are more complex. You’ll be led into thinking you need to do bind and prepare as separate steps when there’s no need.

[TimStreater]

@TimStreater

It’s much more legible and much less chance of screwing up the string with your fifty billion quote marks.

Moderator Edit: Inserted missing end quote.

Now there’s irony for you.

[MarkusR]

you can use it this way with the constuctor
thedb as New SQLiteDatabase(thedbFile)
if you subclass this SQLiteDatabase class you can add your own methods there.
its better to use singular table names.

[José_María_Terry_Jiménez]

Bobby Tables?

https://xkcd.com/327/