使用HttpClient与SSL和证书(Using HttpClient with SSL and certificates)
虽然我已经熟悉HTTPS和SSL的概念，但我最近开始了一些开发，发现我有点困惑。
要求是我编写了一个小型Java应用程序，它运行在连接到扫描仪的机器上。 扫描文档时，将拾取该文档并将文件（通常为PDF）通过Internet发送到我们的应用程序服务器，然后处理该文件。 我使用Apache Commons库和HTTPClient编写了应用程序。
第二个要求是通过SSL连接，需要证书。 按照HTTPclient页面上的指导，我使用了贡献页面中的AuthSSLProtocolSocketFactory。
构造函数可以使用密钥库，密钥库密码，信任库和信任库密码。 作为初步测试，我们的DBA在我们的一个开发Web服务器上启用了SSL，并为我提供了一个.p12文件，当我导入IE时，我可以成功连接。
我在密钥库和信任库之间有点困惑，我需要使用keytool采取什么步骤。 我尝试将p12导入密钥库文件，但得到错误：
keytool error: java.lang.Exception: Input not an X.509 certificate我遵循了将p12导入Internet Explorer并导出为.cer的建议，然后我可以成功导入到密钥库中。 当我把它作为AuthSSLProtocolSocketFactory的keystore参数提供时，我得到了一个毫无意义的错误，但是如果我把它作为一个信任库来尝试它看起来好像读得很好但最终我得到了
Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: bad_certificate我不确定我是否错过了一些步骤，我完全误解了SSL和相互认证，或者这是服务器端的错误配置。
任何人都可以提供建议或指向我可能帮助我解决这个问题的资源吗？
While I've been familiar with HTTPS and the concept of SSL, I have recently begun some development and found I am a little confused.
The requirement was that I write a small Java application that runs on a machine attached to a scanner. When a document is scanned this is picked up and the file (usually PDF) sent over the internet to our application server that will then process it. I've written the application using Apache Commons libraries and HTTPClient.
The second requirement was to connect over SSL, requiring a certificate. Following guidance on the HTTPclient page I am using AuthSSLProtocolSocketFactory from the contributions page.
The constructor can take a keystore, keystore password, truststore and truststore password. As an initial test our DBA enabled SSL on one of our development webservers and provided me with a .p12 file which when I imported into IE allows me to connect successfully.
I am a bit confused between keystores and truststores and what steps I need to take using the keytool. I tried importing the p12 into a keystore file but get the error:
keytool error: java.lang.Exception: Input not an X.509 certificateI followed a suggestion of importing the p12 into Internet Explorer and exporting as a .cer which I can then successfully import into a keystore. When I supply this as a keystore argument of the AuthSSLProtocolSocketFactory I get a meaningless errror, but if I try it as a truststore it seems like it reads it fine but ultimately I get
Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: bad_certificateI am unsure if I have missed some steps, I am misunderstanding SSL and mutual authentication altogether or this is mis-configuration on the server side.
Can anyone provide suggestions or point me towards resources that might help me figure this out please?
原文：https://stackoverflow.com/questions/2774722
最满意答案
您需要在应用程序中执行此操作。 CASE表达式不能用于控制流程。 许多RDBMS为此目的支持IF语句，但SQLite不支持。 SQLite的SQL方言不支持SQL控制流。
对于此问题，您可以尝试使用SQLite的
INSERT OR REPLACE语法 ，但查看您的查询似乎与您尝试执行的操作完全匹配。 您将在碰撞时更新Signature，Mode，BlockID，OutputValue和PoolHeight，而您似乎只想更新BlockID和PoolHeight。您可以使用
INSERT [...] ON CONFLICT UPDATE语句，但我之前没有使用过所谓的UPSERT子句 。 我相信它看起来像这样：INSERT INTO Transactions (Hash, Time, _fROM, Signature, Mode, BlockID, OutputValue, PoolHeight) VALUES('VLEYCBLTDGGLHVQEWWIQ', 1531739096, 'GENESIS', 'GENESIS', - 1, 0, 0, NULL) ON CONFLICT (Hash, Time) DO UPDATE SET BlockID = 0, PoolHeight = NULL WHERE Hash = 'VLEYCBLTDGGLHVQEWWIQ' AND Time = 1531739096;但是，我自己从未在SQLite上使用过这种语句，所以我不确定它的确如何表现。 它似乎要求为CONFLICT指定的列被索引。 我不完全确定WHERE子句在这里是必要的。 阅读文档并首先进行广泛的测试。
另一种选择是每次只运行UPDATE语句，然后如果你得到零记录，则运行INSERT。
You will need to do that in your application. The CASE expression cannot be used for control flow. Many RDBMSs support an IF statement for that purpose, but SQLite does not. SQLite's dialect of SQL does not support control flow with SQL.
For this problem, you can try using SQLite's
INSERT OR REPLACEsyntax, but looking at your queries it does not seem to 100% match what you're trying to do. You'd be updating Signature, Mode, BlockID, OutputValue, and PoolHeight on a collision, while it looks like you only want to update BlockID and PoolHeight.You may be able to use an
INSERT [...] ON CONFLICT UPDATEstatement, but I haven't used the so-called UPSERT clause before. I believe it would look like this:INSERT INTO Transactions (Hash, Time, _fROM, Signature, Mode, BlockID, OutputValue, PoolHeight) VALUES('VLEYCBLTDGGLHVQEWWIQ', 1531739096, 'GENESIS', 'GENESIS', - 1, 0, 0, NULL) ON CONFLICT (Hash, Time) DO UPDATE SET BlockID = 0, PoolHeight = NULL WHERE Hash = 'VLEYCBLTDGGLHVQEWWIQ' AND Time = 1531739096;However, I've never used this statement myself on SQLite, so I'm not sure of exactly how it behaves. It does seem to require that the columns specified for CONFLICT are indexed. I'm not entirely sure the WHERE clause is even necessary here. Read the doc and do extensive testing first.
The other option would be to simply run the UPDATE statement every time, and then if you get zero records affected run the INSERT.
相关问答
更多-
插入如果不存在更新更新？(INSERT IF NOT EXISTS ELSE UPDATE?)[2023-09-03]
看看http://sqlite.org/lang_conflict.html 。 你想要的东西： insert or replace into Book (ID, Name, TypeID, Level, Seen) values ((select ID from Book where Name = "SearchName"), "SearchName", ...); 请注意，如果表中已存在该行，任何未插入列表的字段将被设置为NULL。 这就是为什么ID列有一个子选择：在替换的情况下，该语句将其设置为NU ... -
假设表中有3列。ID，NAME，ROLE BAD：这将插入或替换ID = 1的新值的所有列： INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, 'John Foo', 'CEO'); BAD：这将插入或替换2列... NAME列将被设置为NULL或默认值： INSERT OR REPLACE INTO Employee (id, role) VALUES (1, 'code monkey'); GOOD：这将更新2列。 当 ...
-
MySQL为此提供了两个选项： replace into insert ... on duplicate update 请注意，在这两种情况下，您都必须删除setting_id （无论如何这似乎毫无意义）并使用复合主键。 MySQL offers two options for this: replace into insert ... on duplicate update Do note that in both cases you'll have to drop your setting_id (wh ...
-
是的，你可以用一个查询来做到这一点。 INSERT ON CONFLICT IGNORE应该帮助你： http ： //www.sqlite.org/lang_conflict.html 在名称上放置一个唯一键，如果名称已存在，则在尝试插入记录时会产生冲突。 默认值为ABORT，因此如果没有IGNORE，语句将返回错误。 如果您不想这样，请使用IGNORE。 Yes, you can do that with a single query. INSERT ON CONFLICT IGNORE should ...
-
如果记录存在，如何更新如果不在SQLite中则插入？(How to do Update if record exists and insert if not in SQLite?)[2023-08-23]
您需要在应用程序中执行此操作。 CASE表达式不能用于控制流程。 许多RDBMS为此目的支持IF语句，但SQLite不支持。 SQLite的SQL方言不支持SQL控制流。 对于此问题，您可以尝试使用SQLite的INSERT OR REPLACE语法 ，但查看您的查询似乎与您尝试执行的操作完全匹配。 您将在碰撞时更新Signature，Mode，BlockID，OutputValue和PoolHeight，而您似乎只想更新BlockID和PoolHeight。 您可以使用INSERT [...] ON CO ... -
如何更新记录，否则在MySQL中插入新记录(How to Update a record if it exists otherwise insert a new record in MySQL)[2022-05-18]
DECLARE theCount INT; SELECT COUNT(*) INTO theCount FROM phone_calls WHERE account_id = 8 AND call_code_id = 5 AND status = 1 IF(theCount=0) THEN -- DoLogic; ELSE -- DoSomeOtherLogic; END IF DECLARE theCount INT; SELECT COUNT(*) INTO theCount FROM phone_c ... -
您可以使用这样的代码块 -(NSString*) GetDatabasePath { NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) ; NSString *documentsDirectory = [paths objectAtIndex:0] ; return [documentsDirectory stringByA ...
-
如何使用标准sql或sqlite检查记录是否存在[重复](How to check if a record exists using standard sql or sqlite [duplicate])[2022-01-01]
尝试使用REPLACE命令。 它基本上在文档中称为INSERT或REPLACE的别名。 所以现在，你只需要做REPLACE record 。 希望这可以帮助！ :) Try using REPLACE command. It basically is called an alias for INSERT or REPLACE in the documentation. So now, you just do REPLACE record. Hope this helps! :) -
您可以使用REPLACE（ https://www.sqlite.org/lang_insert.html ） REPLACE INTO chat_channel (channel_name, last_text) VALUES(?,?) 但是，要使其正常工作，您需要在请求中包含唯一或主键（在您的情况下，如果channel_name是chat_channel的主键，或者如果它是唯一的，则可以使用），请参阅https：//www.sqlite .org / lang_conflict.html有关REPLA ...
-
您可以使用SELECT执行此操作，但首先尝试UPDATE会更简单： c.execute("UDPATE X SET A = ?, C = ?, D = D + 1 WHERE B = ?", [newA, newC, oldB]) if c.rowcount == 0: c.execute("INSERT INTO X(A,B,C,D) VALUES (?,?,?,?)", [...]) You could do it with a SELECT, but it would be simpler ...