Best way to find whether table exists

[phppup]

What is the most efficient way to determine whether a table exists?

I've seen statements that using  CREATE TABLE IF NOT EXIST is an indication that one is not properly maintaining a database. But it seems to me that part of a proper maintenance plan would include an immediate response if a table that should be in the db was to suddenly vanish.

How can I code to alert (and repair) for a possible hack/compromising of data.

I've seen information that explains to SELECT record 1 with the understanding that if there are records, then the table MUST exist. But to the contrary, just because there are NO RECORDS would not indicate that the table is non-existent, only that there are no records.

Am I being too nitt-picky?

Edited July 2, 2022 by phppup

[Barand]

If a query attempts to access a table that no longer exists

mysql> select * from my_test;
Empty set (0.01 sec)

mysql> drop table my_test;
Query OK, 0 rows affected (0.18 sec)

mysql> select * from my_test;
ERROR 1146 (42S02): Table 'test.my_test' doesn't exist

 

[phppup]

Better than checking the schema?

[Barand]

How paranoid are you - how often do you intend checking the schema?

What if the table disappears just after your latest check?

[phppup]

How paranoid should I be?

I've never actually dealt with a hacker threat.

Is there a Best Practices for recovery?

[kicken]

1 hour ago, phppup said:

Is there a Best Practices for recovery?

Have backups and a process to restore them.  Also verify your backups periodically by attempting to restore them.  Make sure that when your scripts fail due to the hacker dropping the table they don't spit out error details for the hacker to then see, just show a generic error.

 

Trying to check and re-create tables is a bit silly as if the table suddenly disappeared then so did all it's data.  You can easily re-create the table, but you can't just re-generate the data that was in it, you have to restore that from a backup.

The only time I bother with checking if tables/columns exist are for scripts that get run to upgrade a database to a newer schema.  Even then, I do this primarily for ease of development as it allows me to simply re-run the script when I make more changes rather than having to undo the last run somehow first.  I use SQL Server though so I can't really answer how to accomplish such a task in MySql.

 

[phppup]

While on the subject of being paranoid, what characters are safe to use [or totally unsafe] for usernames and other text inputs?

Is a Reg Ex excluding < > ? adequate?

(probably not, but is there a basic template to use all around?)

[requinix]

Characters are unsafe according to how you (mis)use them. Preventing people from using < > because it would mess up your HTML is absolutely the wrong solution.

If you want to prevent people from using < > because you don't think usernames should contain those kinds of symbols, that's something entirely different.

[phppup]

10 minutes ago, requinix said:

ou don't think usernames should contain those kinds of symbols,

Well, there's that.

But also the degree of security to thwart malicious efforts and hacking attempts.

Let's say for a comment text area to be stored in a db.

[requinix]

47 minutes ago, phppup said:

But also the degree of security to thwart malicious efforts and hacking attempts.

No. Stop thinking that. Restricting people from typing certain things is not security.

In fact it's the opposite of security because now you've told the rest of the internet that you have a very specific vulnerability when it comes to rendering certain user-provided values in your website.

47 minutes ago, phppup said:

Let's say for a comment text area to be stored in a db.

Doesn't matter. You could be storing the username in a database, or a text file, or you could even be emailing yourself a copy of their registration. Does not matter.

[phppup]

@requinix I understand what you're saying, but at the same time, isn't allowing every keyboard symbol in an input field also offering a skeleton key to a hacker-wannabe.

Yes, I expect a person's name to contain only letters (unless you're Elon Musk's kid).

But aren't there certain symbols that effectively take the challenge away from a malicious effort if they are accessible (ie: opening tags for code), especially when that code is headed for a database.

Or should my question be: what's the best way to secure a db?

Edited July 8, 2022 by phppup

[requinix]

3 hours ago, phppup said:

@requinix I understand what you're saying, but at the same time, isn't allowing every keyboard symbol in an input field also offering a skeleton key to a hacker-wannabe.

No. It doesn't.

3 hours ago, phppup said:

Yes, I expect a person's name to contain only letters (unless you're Elon Musk's kid).

Then you'd be wrong.

3 hours ago, phppup said:

But aren't there certain symbols that effectively take the challenge away from a malicious effort if they are accessible (ie: opening tags for code), especially when that code is headed for a database.

You're thinking about this in a very narrow-minded way.

Imagine this. You are walking along in a neighborhood and see a house. In the window is a sign that says "Do not break and enter through this window". What do you think when you read that?

3 hours ago, phppup said:

Or should my question be: what's the best way to secure a db?

Don't let it be open to the internet and use prepared statements.

Of course < > symbols have nothing to do with that, so I'm going to assume you also meant to ask "what's the best way to secure my website from XSS" and say to use functions like htmlspecialchars() when outputting unsafe strings.