WHILE语句中的MySQL语法错误(MySQL syntax error in WHILE statement)

 所有安全都是一种权衡。 
 
 （我不是加密/安全专家。这些是我对我所阅读和研究的理解，但如果你正在做任何重要的事情，请从那些真正做过这件事的人那里获得专业建议）。 
 
 在这种情况下，您有许多选择，主要区别在于他们如何通过关键的盗窃/滥用风险来平衡正常运行时间/便利性。 我假设您使用的是GnuPG / OpenPGP库，而不是命令行工具，但如果不是“应用程序”，则可以将其视为GnuPG代理。 
 
 
 
 将密钥存储在磁盘上未加密。 应用程序可以随时使用密钥。 如果重新启动应用程序，则可以立即访问该密钥。 闯入系统或窃取（未加密）备份的攻击者可以轻松使用该密钥。 适当的备份加密至关重要。 
 
 
 对这种方法的一个微小改进是存储加密的密钥，并将密钥的（混淆的）密码存储在系统/ app二进制文件的其他地方; 它让攻击者的生活变得更加困难，并且意味着他们至少要花更多的时间在它上面，但在大多数情况下，他们仍然可以很容易地恢复它。 适当的备份加密至关重要。 
 
 
 将加密的密钥存储在磁盘上，并在应用启动时将其解密存储在内存中。 在应用启动期间提示时，人可以解密密钥; 之后，应用程序可以随时使用密钥。 从磁盘/备份中窃取密钥对攻击者来说没有什么好处，他们不得不额外努力从应用程序的内存中恢复密钥，或者在崩溃后由管理员输入时修改/包装应用程序以捕获密码短语重新开始。 密钥必须锁定在无法换出的内存中。 
 
 
 将加密的密钥存储在磁盘上，并仅使用特定的管理员交互对其进行解密。 没有管理员干预，应用程序无法使用密钥。 磁盘上非常安全的密钥和应用程序内存的盗窃风险受到内存中短时间的限制。 但是，已经进入系统的攻击者仍然可以修改应用程序以在解密时记录密钥，或者捕获密码短语。 密钥必须锁定在无法换出的内存中。 
 
 
 将密钥存储在可移动存储上。 在app启动时将其物理插入以解密密钥并将其存储在应用程序内存中，如（3），或者当应用程序实际需要使用密钥时（4）。 这使攻击者更难以窃取加密密钥并使密码盗窃变得不那么有用，但没有更难修改应用程序以窃取解密密钥。 他们也可以等到他们看到存储插入并复制加密密钥，如果他们用包装器/键盘记录器等窃取了密码。 IMO对于磁盘上加密密钥的强大密码并没有多大好处 - 它可能会让攻击者的生活变得更加困难，但对管理员而言却要困难得多。 
 
 
 将密钥存储在智能卡，加密加速器或USB加密设备上，该设备永远不会允许密钥暴露，只能使用它执行加密操作。 PKCS＃11标准得到广泛支持并且对此非常有用。 密钥（理论上）不能在没有物理窃取​​硬件的情况下被窃取 - 在许多硬件上存在密钥提取攻击，但大多数需要大量时间，并且通常需要物理访问。 服务器可以随意使用密钥（如果加速器没有超时/解锁）或仅使用管理员干预（如果加速器在每次使用后被锁定并且必须由管理员解锁）。 攻击者仍然可以通过伪装成应用程序来使用加速器解密数据，但是他们必须做更多的工作，并且需要持续访问目标系统。 当然，这个花费更多。 
 对于此选项，灾难恢复更具挑战性; 您依靠物理硬件来解密数据。 如果数据中心烧毁，你就完成了。 因此，您需要重复和/或非常安全地存储密钥的副本。 当然，每个重复都会增加风险，特别是那些插入“以防万一”备份服务器的人我们并没有真正使用它们并且不会使安全补丁保持最新...... 
 如果您使用内置密钥而不是可以存储但未读取密钥的硬件，那么您将面临额外的挑战，即有一天硬件将被淘汰。 曾经试图获得需要在现代服务器上运行ISA卡的关键业务软件吗？ 它很有趣 - 有一天，PCI / X和USB也会像这样。 当然，到那时你正在使用的加密系统可能会被破坏，所以你需要解密所有数据并将其迁移到另一个设置。 不过，我会使用硬件，我可以生成密钥，将其编程到硬件中，并将原始密钥以几种不同的形式存储在银行保险箱中。 
 
 
 现在你已经读过了，请记住：我只是一个感兴趣的甚至是业余爱好者。 去问专业人士。 当他们告诉你我有多么不对劲时，请来这里解释一下:-) 
 
 无论你做什么，都不要发明自己的加密系统。 

All security is a trade-off.
 
(I'm not a crypto/security expert. These are my understandings from what I've read and studied, but if you're doing anything important, get professional advice from someone who's done this a lot for real).
 
In this case you have a number of choices that differ mainly on how they trade off uptime/convenience with key theft/abuse risk. I'm assuming you're using a GnuPG/OpenPGP library, rather than the command-line tools, but if not "the app" can be considered the GnuPG agent.
 
 
 
Store the key un-encrypted on disk. The app can use the key whenever it wants. If the app is restarted, it has immediate access to the key. An attacker that breaks into the system or steals an (unencrypted) backup can use the key easily. Proper backup encryption is vital.
 
 
A marginal improvement over this approach is to store the key encrypted and store the (obfuscated) passphrase for the key elsewhere in the system / in the app binary; it makes life a bit harder for the attacker and means they at least have to spend more time on it, but in most cases they'll still be able to recover it pretty easily. Proper backup encryption is vital.
 
 
Store the key encrypted on disk and store it decrypted in memory on app startup. A human can decrypt the key when prompted during app startup; after that, the app can use the key whenever it wants. Theft of the key from disk / backups does the attacker little good, they have to go to the extra effort of recovering the key from the application's memory, or modifying/wrapping the application to capture the passphrase when entered by the administrator after a crash/restart. Key must be locked in memory that cannot be swapped out.
 
 
Store the key encrypted on disk and decrypt it only with specific administrator interaction. The app cannot use the key without an admin intervening. The key pretty safe on disk and the theft risk from app memory is limited by the short periods it's in memory. However, an attacker that has broken into the system can still modify the app to record the key when it'd decrypted, or capture the passphrase. Key must be locked in memory that cannot be swapped out.
 
 
Store the key on removable storage. Physically insert it on app startup to decrypt the key and store it in app memory like (3), or when the app actually needs to use the key like (4). This makes it a bit harder for the attacker to steal the encrypted key and makes password theft less useful, but no harder to modify the app to steal the decrypted key. They can also just wait until they see the storage inserted and copy the encrypted key if they've stolen the passphrase with a wrapper/keylogger/etc. IMO it's not much benefit over a strong passphrase for the encrypted key on disk - it might make life a little harder on the attacker, but it's a lot harder on the admin.
 
 
Store the key on a smartcard, crypto accelerator, or USB crypto device that's designed never to permit the key to be exposed, only to perform crypto operations using it. The PKCS#11 standard is widely supported and useful for this. The key (theoretically) cannot be stolen without physically stealing the hardware - there are key extraction attacks on lots of hardware, but most require lots of time, and often require physical access. The server can use the key at will (if the accelerator has no timeout/unlock) or only with admin intervention (if the accelerator is locked after each use and must be unlocked by the admin). The attacker can still decrypt data using the accelerator by masquerading as the app, but they've got to do a lot more work, and will need to have ongoing access to the target system. Of course, this one costs more.
 
Disaster recovery is more challenging for this option; you depend on physical hardware for decrypting your data. If the data center burns down, you're done for. So you need duplicates and/or a very securely stored copy of the key its self. Every duplicate adds risk, of course, especially the one plugged into that "just in case" backup server we don't really use and don't keep the security patches up to date on...
 
If you go for hardware with a key built-in rather than one where you can store but not read the key, you have the added challenge that one day that hardware will be obsolete. Ever tried to get business critical software that requires an ISA card running on a modern server? It's fun - and one day, PCI/X and USB will be like that too. Of course, by then the crypto system you're using might be broken anyway, so you'll need to decrypt all your data and migrate it to another setup anyway. Still, I'd be using hardware where I can generate a key, program it into the hardware, and store the original key in a couple of different forms in a bank safe deposit box.
 
 
Now that you've read that, remember: I'm just an interested not-even-hobbyist. Go ask a professional. When they tell you how totally wrong I am, come here and explain :-)
 
Whatever you do, DO NOT invent your own crypto system.