trigger – Stored procedure to change string to datetime in MySQL

This is a follow up to this question. Turns out the driver that I am using (can’t change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my MySQL 8.0 database as a string type. There is no native function within the software I am using to read in the PLC and write into the db that can change a string to a timestamp.

What I’m trying to figure out now, is how to set up a trigger that converts the string to a timestamp on insert. This idea was suggested to me by the help hotline of the software, but if you have any better ideas, I’m open to that too. Thus far, I have 2 columns: one where I “receive” the string which is of type VARCHAR(30), and then another one where I write the timestamp value with the trigger.

This setup feels super stupid to me because I end up with 2 columns with the same data just in different formats, and one of them is completely useless.

Thus far, my table looks like:

CREATE TABLE `test` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `Date_VARCHAR` varchar(30) DEFAULT NULL,
  `Date_TIMESTAMP` timestamp(6) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52383 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

And the trigger is:

DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `db_test`.`test_BEFORE_INSERT`     
    BEFORE INSERT ON `db_test`.`test` FOR EACH ROW
    BEGIN  
    SET NEW.Date_TIMESTAMP = NEW.Date_VARCHAR ;
    END$$
DELIMITER ;

Is there a better way of doing this to avoid having the same data twice? I would like to make a trigger that just converts the data and writes it all in one column instead of the setup I have come up with.

Here’s a quick look at what I have going on . I have done an insert of some random values, but it’s actually all being done with the datalogger plugin of KEPServerEx.

oneplus6t – How can I find the oldest SMS that is stored on my phone?

How can I find the oldest SMS that is stored on my phone?

Since SMSs are threaded I can’t just scroll all the way down to find my oldest message.

The reason I want to do this is that I use a backup solution for SMS that, for unknown reasons, has failed to copy some messages from my current phone. I therefore want to startover and make a full backup of my current phine. At the same time I have a decade worth of history backed up in this solution and I just want to cull the messages that is on my current phone.

Basically, in my backup solution I have messages 1 to 1200. 1 to 1000 are old, from old phones, while 1001 to 1200 are on my current phone. I want to find 1001 and then delete all messages newer than that from the backup. Finally I will backup 1001 and newer again.

Android 10 on a Oneplus 6T

http – How is httpOnly cookie stored?


Your privacy


By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.




Ingress client certificate authenticate requires CA certificate to be stored in secret?

I want to enable client-certificate authentication in my AKS cluster and I have a basic question which I just don’t seem to understand. As per the docs, ingress requires the CA certificate to be stored in a secret. My question is: Assuming that I use client-certificates that have been issued by a trusted CA (that’s how it works right? CAs issue client-certificates that they sign?), why would a trusted CA give me their CA certificate to be stored in AKS cluster as a secret? Do CAs just hand out their certificates out to public? Isn’t that a security issue? (since I can sign client-certificates using that CA certificate)

stored procedures – Concat a string to a variable to use as a column name in a query

I have a procedure where I query table a and create variables like this

SELECT TRCODE , COLA , COLB , COLC , COLD
INTO TRANSACTION_TYPE , HOURS , RATE , FACTOR , APPROPRIATION
FROM PR . PREXEC
WHERE USRID = USER_NUMBER ;

The appropriation variable contains one character and it is a number from 1-4.

I have another table called pr.earnings. In that table I have a columns named APPROP1 through APPROP4. In this same procedure I need to query pr.earnings for by taking the string ‘APPROP’ and concatenating the APPROPRIATION variable from the query.

I tried

select concat('APPROP',APPROPRIATION)

FROM PR.EARNIGS;

The result I want is select APPROP1 FROM PR.EARNINGS; What happens is I get a system created column with APPROP1 inside it. I can’t figure out how to concat a string to a variable to use in a query.

I tried a using dynamic sql but kept getting errors because I don’t know what I’m doing.
How do I concat a string and a variable to use it as a column name in an sql query?

sql server – How can I use multiple primary keys in this stored procedure?

In the below stored procedure, the primary key is coming from a lookup table which stores the primary key columns. They can be one or more than one. Can someone help, how can I make it work for multiple primary keys?

Set @Query1 = N'DELETE STG.(' + @StageTable + ') FROM STG.' + @StageTable + ' a INNER JOIN LANDING.' + @LandingTable +
                            ' b ON a.' + @PrimaryKeys + ' = b.' + @PrimaryKeys
PRINT (@Query1)
EXEC sp_executesql @Query1

See, in the below table the Primary Key column will hold the primary keys of the table.

ID | ORACLESCHEMA|ORACLETABLE|DEVDWSCHEMA|DEVDWTABLE|COLUMNLIST|PrimaryKeys
1  | APPS        | SAMPLE    | DEVDW     | SAMPLE   | NULL     | ID, NAME
2  | AP          | SAMPLE2   | DEVDW     | SAMPLE2  | NULL     | ID,NAME,ORDER

The column list contains the list of columns in the table

Where is the PDF encryption key stored

Assuming you have the user protected PDF file. Using the password, the cryptographic key is generated and the file encrypted. Where is the key stored, is it on the RAM every time the file is opened?

Messages asking me permission to access the proxy data stored in keychain

When connected to my office network, I keep getting messages asking me permission to access the proxy data stored in keychain. They keep popping up every few minutes (even seconds), no matter what I do.
I enter my keychain password and click on “Always”, but the messages still appear. I click on “Deny” and they still come back!
And if I click on “Deny”, nothing seems to happen: everything works fine alla the same… These messages look like they are perfectly useless…
I attach a screenshot.
enter image description here

sql server – Why is part of transaction committed when stored procedure faults?

Transactions don’t automatically roll back on error–that’s not what they are designed to do. They are designed to give you the ability to rollback. However, you still need to do something to make that happen.

As you mention, you can make that happen through TRY...CATCH, which gives you the most control over if and how you can rollback.

It sounds like you are expecting the behavior of SET XACT_ABORT ON, which you can set in your stored procedure, but is not the default behavior. The description of setting XACT_ABORT on vs off from the docs is:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.

encryption – Passwords stored as obfuscated text, not encrypted

First thing I’d do is consider whether the system needs “reversibly encrypted” passwords at all (usually yes if it’s sending them on to some other service rather than just verifying them when a user logs in, sometimes yes if this is required by some important customer but they should have an option to properly hash them as well). Second, since you say “a simple mapping” I assume this isn’t actually using any modern cryptographic cipher primitives (AES, *fish, SALSA20, etc.), so that’s definitely a security bug you can file.

Look up a security contact (email address, etc.). There should be one somewhere on the site. If you can’t find one, try emailing security@company.domain, or just contact their support line and ask for a security contact.

Note that any form of reversible encryption, no matter how up-to-date its ciphers or strong its keys, suffers from a key storage problem: the program needs access to the key, which means anybody who can access the program itself can almost certainly decrypt the data. However, there are still improvements to be made from using real encryption:

  • Real encryption, even with a hardcoded key, will prevent anybody who doesn’t know the key from reversing the encryption if they get access to the DB. It sounds like they currently don’t even meet this – very low – bar.
  • Done correctly, the key should be unique per instance of the app. Getting access to somebody else’s DB shouldn’t reveal anything, even if you know the encryption key used by your own copy/instance of the software.
  • The key should be stored in a location as hard to access as possible. Ideally, it would be stored somewhere not actually extractable (like an HSM), with the app having the ability to request encryption and decryption of arbitrary strings but no other software allowed to access the HSM. At the very least the key needs to be separate from the DB, such that even an attacker with total, unfettered DB access can’t get the key without finding a new vulnerability in some other part of the system.

It sounds like you’re already well aware of why they should be using a slow password hashing function, rather than reversible encryption of any sort. Even if they need encryption for some passwords/API keys (stuff used to access external services, not to authenticate local users), they should use encryption for those secrets only, and use secure password hashing algorithms for user passwords.

If the vendor won’t budge – says that it’s not a security bug, or that they don’t care, or just refuses to respond – give them some time and then (IMO) it’s time to escalate. If possible for you, try to convince your company to threaten to cancel the contract; that’s often the simplest leverage. If you can’t, I would tend to move up to name and shame. Companies are usually way more likely to respond to things when it’s likely to impact their bottom line, and bad publicity can do that. Sites like https://plaintextoffenders.com/, or just reaching out publicly on social media (especially to, or at least mentioning, well-known security figures), can help get the word out.

Obviously that last part isn’t risk-free. There’s probably something in the terms of use about not “reverse engineering” the software, and although I think this level of “cryptanalysis” doesn’t count at all, I am not a lawyer. If you had to bypass any attempted safeguards to keep you out of the DB – entering a username/password of admin/admin might count, though copying a DB connection string out of a plain-text config file on a system you control does not – then that increases the risk they’d think it worthwhile to involve lawyers. A smart company wouldn’t do this – siccing the law on somebody who is trying to responsibly report a security issue is a good way to get the entire security community mad at you, and some of us hold grudges and make product recommendations at big companies (and others are hacktivists) – but a smart company wouldn’t let things get nearly that far to begin with. Before you take any steps beyond just reporting the issue to the vendor, especially if you have any notion of involving your company’s name, you might want to talk to the legal department. However, I am not a lawyer, and this is NOT legal advice.