{"action":"created","comment":{"url":"https://api.github.com/repos/dimagi/commcare/pulls/comments/42486684","id":42486684,"diff_hunk":"@@ -0,0 +1,151 @@\n+package org.commcare.modern.database;\n+\n+import org.commcare.modern.models.EncryptedModel;\n+import org.commcare.modern.models.RecordTooLargeException;\n+import org.commcare.modern.util.Pair;\n+import org.javarosa.core.services.storage.IMetaData;\n+import org.javarosa.core.services.storage.Persistable;\n+import org.javarosa.core.util.externalizable.Externalizable;\n+\n+import java.io.ByteArrayOutputStream;\n+import java.io.DataOutputStream;\n+import java.io.IOException;\n+import java.util.ArrayList;\n+import java.util.HashMap;\n+import java.util.HashSet;\n+import java.util.List;\n+import java.util.Set;\n+\n+/**\n+ * Setup of platform-agnostic DB helper functions IE for generating SQL\n+ * statements, args, content values, etc.\n+ *\n+ * @author wspride\n+ *\n+ */\n+public class DatabaseHelper {\n+\n+    public static final String ID_COL = \"commcare_sql_id\";\n+    public static final String DATA_COL = \"commcare_sql_record\";\n+\n+    public static Pair<String, String[]> createWhere(String[] fieldNames, Object[] values,  Persistable p)  throws IllegalArgumentException {\n+        return createWhere(fieldNames, values, null, p);\n+    }\n+\n+    public static Pair<String, String[]> createWhere(String[] fieldNames, Object[] values,  EncryptedModel em, Persistable p)  throws IllegalArgumentException {\n+        Set<String> fields = null;\n+        if(p instanceof IMetaData) {\n+            IMetaData m = (IMetaData)p;\n+            String[] thefields = m.getMetaDataFields();\n+            fields = new HashSet<String>();\n+            for(String s : thefields) {\n+                fields.add(TableBuilder.scrubName(s));\n+            }\n+        }\n+\n+\n+        if(em instanceof IMetaData) {\n+            IMetaData m = (IMetaData)em;\n+            String[] thefields = m.getMetaDataFields();\n+            fields = new HashSet<String>();\n+            for(String s : thefields) {\n+                fields.add(TableBuilder.scrubName(s));\n+            }\n+        }\n+\n+\n+        StringBuilder stringBuilder = new StringBuilder();\n+        ArrayList<String> arguments = new ArrayList<String>();\n+        boolean set = false;\n+        for(int i = 0 ; i < fieldNames.length; ++i) {\n+            String columnName = TableBuilder.scrubName(fieldNames[i]);\n+            if(fields != null) {\n+                if(!fields.contains(columnName)) {\n+                    continue;\n+                }\n+            }\n+\n+            if(set){\n+                stringBuilder.append(\" AND \");\n+            }\n+\n+            stringBuilder.append(columnName);\n+            stringBuilder.append(\"=?\");\n+\n+            arguments.add(values[i].toString());\n+\n+            set = true;\n+        }\n+\n+        String[] retArray = new String[arguments.size()];\n+        for(int i =0; i< arguments.size(); i++){\n+            retArray[i] = arguments.get(i);\n+        }\n+\n+        return new Pair<String, String[]>(stringBuilder.toString(), retArray);\n+    }\n+\n+    public static Set<String> getMetaDataFields(Persistable p){\n+        Set<String> fields = null;\n+        if(p instanceof IMetaData) {\n+            IMetaData m = (IMetaData)p;\n+            String[] thefields = m.getMetaDataFields();\n+            fields = new HashSet<String>();\n+            for(String s : thefields) {\n+                fields.add(TableBuilder.scrubName(s));\n+            }\n+        }\n+        return fields;\n+    }\n+\n+\n+    public static HashMap<String, Object> getMetaFieldsAndValues(Externalizable e) throws RecordTooLargeException{\n+\n+        ByteArrayOutputStream bos = new ByteArrayOutputStream();\n+\n+        try {\n+            e.writeExternal(new DataOutputStream(bos));\n+            bos.close();\n+        } catch (IOException e1) {\n+            e1.printStackTrace();\n+            throw new RuntimeException(\"Failed to serialize externalizable for content values\");\n+        }\n+        byte[] blob = bos.toByteArray();\n+\n+        HashMap<String, Object> values = new HashMap<String, Object>();\n+\n+        if(e instanceof IMetaData) {\n+            IMetaData m = (IMetaData)e;\n+            for(String key : m.getMetaDataFields()) {\n+                Object o = m.getMetaData(key);\n+                if(o == null ) { continue;}\n+                String value = o.toString();\n+                values.put(TableBuilder.scrubName(key), value);\n+            }\n+        }\n+        if(blob.length > 1000000){\n+            throw new RecordTooLargeException(blob.length / 1000000);\n+        }\n+        values.put(DATA_COL, blob);\n+\n+        return values;\n+    }\n+\n+    public static String getTableCreateString(String storageKey, Persistable p){\n+        TableBuilder mTableBuilder = new TableBuilder(storageKey);\n+        mTableBuilder.addData(p);\n+        return mTableBuilder.getTableCreateString();\n+    }\n+\n+    public static Pair<String, List<Object>> getTableInsertData(String storageKey, Persistable p){\n+        TableBuilder mTableBuilder = new TableBuilder(storageKey);\n+        mTableBuilder.addData(p);\n+        return mTableBuilder.getTableInsertData(p);\n+    }\n+\n+    public static String getRecordForValueSelectString(String storageKey, String id){\n+        return \"SELECT \" + DatabaseHelper.DATA_COL + \" from \" + storageKey + \" WHERE commcare_sql_id = \" + id + \";\";","path":"modern/org/commcare/modern/database/DatabaseHelper.java","position":147,"original_position":147,"commit_id":"385c2f0537514f6b0b7519bc573bdfce5574cfed","original_commit_id":"385c2f0537514f6b0b7519bc573bdfce5574cfed","user":{"login":"ctsims","id":155066,"avatar_url":"https://avatars.githubusercontent.com/u/155066?v=3","gravatar_id":"","url":"https://api.github.com/users/ctsims","html_url":"https://github.com/ctsims","followers_url":"https://api.github.com/users/ctsims/followers","following_url":"https://api.github.com/users/ctsims/following{/other_user}","gists_url":"https://api.github.com/users/ctsims/gists{/gist_id}","starred_url":"https://api.github.com/users/ctsims/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ctsims/subscriptions","organizations_url":"https://api.github.com/users/ctsims/orgs","repos_url":"https://api.github.com/users/ctsims/repos","events_url":"https://api.github.com/users/ctsims/events{/privacy}","received_events_url":"https://api.github.com/users/ctsims/received_events","type":"User","site_admin":false},"body":"This looks injection vulnerable unless id _always_ comes from our code directly.","created_at":"2015-10-20T12:09:39Z","updated_at":"2015-10-20T12:09:39Z","html_url":"https://github.com/dimagi/commcare/pull/101#discussion_r42486684","pull_request_url":"https://api.github.com/repos/dimagi/commcare/pulls/101","_links":{"self":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/comments/42486684"},"html":{"href":"https://github.com/dimagi/commcare/pull/101#discussion_r42486684"},"pull_request":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/101"}}},"pull_request":{"url":"https://api.github.com/repos/dimagi/commcare/pulls/101","id":39971873,"html_url":"https://github.com/dimagi/commcare/pull/101","diff_url":"https://github.com/dimagi/commcare/pull/101.diff","patch_url":"https://github.com/dimagi/commcare/pull/101.patch","issue_url":"https://api.github.com/repos/dimagi/commcare/issues/101","number":101,"state":"open","locked":false,"title":"SQLite API","user":{"login":"wpride","id":2293064,"avatar_url":"https://avatars.githubusercontent.com/u/2293064?v=3","gravatar_id":"","url":"https://api.github.com/users/wpride","html_url":"https://github.com/wpride","followers_url":"https://api.github.com/users/wpride/followers","following_url":"https://api.github.com/users/wpride/following{/other_user}","gists_url":"https://api.github.com/users/wpride/gists{/gist_id}","starred_url":"https://api.github.com/users/wpride/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wpride/subscriptions","organizations_url":"https://api.github.com/users/wpride/orgs","repos_url":"https://api.github.com/users/wpride/repos","events_url":"https://api.github.com/users/wpride/events{/privacy}","received_events_url":"https://api.github.com/users/wpride/received_events","type":"User","site_admin":false},"body":"Cross-requested with https://github.com/dimagi/commcare-odk/pull/668 and https://github.com/dimagi/javarosa/pull/126","created_at":"2015-07-14T22:51:17Z","updated_at":"2015-10-20T12:09:39Z","closed_at":null,"merged_at":null,"merge_commit_sha":"6ab73c977082f9a6cd9f540f51cb2b3568c7f6bc","assignee":null,"milestone":{"url":"https://api.github.com/repos/dimagi/commcare/milestones/2","html_url":"https://github.com/dimagi/commcare/milestones/commcare_2.24","labels_url":"https://api.github.com/repos/dimagi/commcare/milestones/2/labels","id":1276471,"number":2,"title":"commcare_2.24","description":null,"creator":{"login":"phillipm","id":94817,"avatar_url":"https://avatars.githubusercontent.com/u/94817?v=3","gravatar_id":"","url":"https://api.github.com/users/phillipm","html_url":"https://github.com/phillipm","followers_url":"https://api.github.com/users/phillipm/followers","following_url":"https://api.github.com/users/phillipm/following{/other_user}","gists_url":"https://api.github.com/users/phillipm/gists{/gist_id}","starred_url":"https://api.github.com/users/phillipm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/phillipm/subscriptions","organizations_url":"https://api.github.com/users/phillipm/orgs","repos_url":"https://api.github.com/users/phillipm/repos","events_url":"https://api.github.com/users/phillipm/events{/privacy}","received_events_url":"https://api.github.com/users/phillipm/received_events","type":"User","site_admin":false},"open_issues":2,"closed_issues":15,"state":"open","created_at":"2015-08-27T20:11:31Z","updated_at":"2015-10-16T08:46:00Z","due_on":null,"closed_at":null},"commits_url":"https://api.github.com/repos/dimagi/commcare/pulls/101/commits","review_comments_url":"https://api.github.com/repos/dimagi/commcare/pulls/101/comments","review_comment_url":"https://api.github.com/repos/dimagi/commcare/pulls/comments{/number}","comments_url":"https://api.github.com/repos/dimagi/commcare/issues/101/comments","statuses_url":"https://api.github.com/repos/dimagi/commcare/statuses/385c2f0537514f6b0b7519bc573bdfce5574cfed","head":{"label":"dimagi:commcare-api","ref":"commcare-api","sha":"385c2f0537514f6b0b7519bc573bdfce5574cfed","user":{"login":"dimagi","id":94514,"avatar_url":"https://avatars.githubusercontent.com/u/94514?v=3","gravatar_id":"","url":"https://api.github.com/users/dimagi","html_url":"https://github.com/dimagi","followers_url":"https://api.github.com/users/dimagi/followers","following_url":"https://api.github.com/users/dimagi/following{/other_user}","gists_url":"https://api.github.com/users/dimagi/gists{/gist_id}","starred_url":"https://api.github.com/users/dimagi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimagi/subscriptions","organizations_url":"https://api.github.com/users/dimagi/orgs","repos_url":"https://api.github.com/users/dimagi/repos","events_url":"https://api.github.com/users/dimagi/events{/privacy}","received_events_url":"https://api.github.com/users/dimagi/received_events","type":"Organization","site_admin":false},"repo":{"id":20939782,"name":"commcare","full_name":"dimagi/commcare","owner":{"login":"dimagi","id":94514,"avatar_url":"https://avatars.githubusercontent.com/u/94514?v=3","gravatar_id":"","url":"https://api.github.com/users/dimagi","html_url":"https://github.com/dimagi","followers_url":"https://api.github.com/users/dimagi/followers","following_url":"https://api.github.com/users/dimagi/following{/other_user}","gists_url":"https://api.github.com/users/dimagi/gists{/gist_id}","starred_url":"https://api.github.com/users/dimagi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimagi/subscriptions","organizations_url":"https://api.github.com/users/dimagi/orgs","repos_url":"https://api.github.com/users/dimagi/repos","events_url":"https://api.github.com/users/dimagi/events{/privacy}","received_events_url":"https://api.github.com/users/dimagi/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/dimagi/commcare","description":"","fork":false,"url":"https://api.github.com/repos/dimagi/commcare","forks_url":"https://api.github.com/repos/dimagi/commcare/forks","keys_url":"https://api.github.com/repos/dimagi/commcare/keys{/key_id}","collaborators_url":"https://api.github.com/repos/dimagi/commcare/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/dimagi/commcare/teams","hooks_url":"https://api.github.com/repos/dimagi/commcare/hooks","issue_events_url":"https://api.github.com/repos/dimagi/commcare/issues/events{/number}","events_url":"https://api.github.com/repos/dimagi/commcare/events","assignees_url":"https://api.github.com/repos/dimagi/commcare/assignees{/user}","branches_url":"https://api.github.com/repos/dimagi/commcare/branches{/branch}","tags_url":"https://api.github.com/repos/dimagi/commcare/tags","blobs_url":"https://api.github.com/repos/dimagi/commcare/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/dimagi/commcare/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/dimagi/commcare/git/refs{/sha}","trees_url":"https://api.github.com/repos/dimagi/commcare/git/trees{/sha}","statuses_url":"https://api.github.com/repos/dimagi/commcare/statuses/{sha}","languages_url":"https://api.github.com/repos/dimagi/commcare/languages","stargazers_url":"https://api.github.com/repos/dimagi/commcare/stargazers","contributors_url":"https://api.github.com/repos/dimagi/commcare/contributors","subscribers_url":"https://api.github.com/repos/dimagi/commcare/subscribers","subscription_url":"https://api.github.com/repos/dimagi/commcare/subscription","commits_url":"https://api.github.com/repos/dimagi/commcare/commits{/sha}","git_commits_url":"https://api.github.com/repos/dimagi/commcare/git/commits{/sha}","comments_url":"https://api.github.com/repos/dimagi/commcare/comments{/number}","issue_comment_url":"https://api.github.com/repos/dimagi/commcare/issues/comments{/number}","contents_url":"https://api.github.com/repos/dimagi/commcare/contents/{+path}","compare_url":"https://api.github.com/repos/dimagi/commcare/compare/{base}...{head}","merges_url":"https://api.github.com/repos/dimagi/commcare/merges","archive_url":"https://api.github.com/repos/dimagi/commcare/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/dimagi/commcare/downloads","issues_url":"https://api.github.com/repos/dimagi/commcare/issues{/number}","pulls_url":"https://api.github.com/repos/dimagi/commcare/pulls{/number}","milestones_url":"https://api.github.com/repos/dimagi/commcare/milestones{/number}","notifications_url":"https://api.github.com/repos/dimagi/commcare/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/dimagi/commcare/labels{/name}","releases_url":"https://api.github.com/repos/dimagi/commcare/releases{/id}","created_at":"2014-06-17T21:13:25Z","updated_at":"2015-05-01T15:55:40Z","pushed_at":"2015-10-19T16:21:16Z","git_url":"git://github.com/dimagi/commcare.git","ssh_url":"git@github.com:dimagi/commcare.git","clone_url":"https://github.com/dimagi/commcare.git","svn_url":"https://github.com/dimagi/commcare","homepage":null,"size":8416,"stargazers_count":2,"watchers_count":2,"language":"Java","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":4,"mirror_url":null,"open_issues_count":4,"forks":4,"open_issues":4,"watchers":2,"default_branch":"master"}},"base":{"label":"dimagi:master","ref":"master","sha":"5479d9f773246c50048aa2a55935a4cf75bc08d8","user":{"login":"dimagi","id":94514,"avatar_url":"https://avatars.githubusercontent.com/u/94514?v=3","gravatar_id":"","url":"https://api.github.com/users/dimagi","html_url":"https://github.com/dimagi","followers_url":"https://api.github.com/users/dimagi/followers","following_url":"https://api.github.com/users/dimagi/following{/other_user}","gists_url":"https://api.github.com/users/dimagi/gists{/gist_id}","starred_url":"https://api.github.com/users/dimagi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimagi/subscriptions","organizations_url":"https://api.github.com/users/dimagi/orgs","repos_url":"https://api.github.com/users/dimagi/repos","events_url":"https://api.github.com/users/dimagi/events{/privacy}","received_events_url":"https://api.github.com/users/dimagi/received_events","type":"Organization","site_admin":false},"repo":{"id":20939782,"name":"commcare","full_name":"dimagi/commcare","owner":{"login":"dimagi","id":94514,"avatar_url":"https://avatars.githubusercontent.com/u/94514?v=3","gravatar_id":"","url":"https://api.github.com/users/dimagi","html_url":"https://github.com/dimagi","followers_url":"https://api.github.com/users/dimagi/followers","following_url":"https://api.github.com/users/dimagi/following{/other_user}","gists_url":"https://api.github.com/users/dimagi/gists{/gist_id}","starred_url":"https://api.github.com/users/dimagi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimagi/subscriptions","organizations_url":"https://api.github.com/users/dimagi/orgs","repos_url":"https://api.github.com/users/dimagi/repos","events_url":"https://api.github.com/users/dimagi/events{/privacy}","received_events_url":"https://api.github.com/users/dimagi/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/dimagi/commcare","description":"","fork":false,"url":"https://api.github.com/repos/dimagi/commcare","forks_url":"https://api.github.com/repos/dimagi/commcare/forks","keys_url":"https://api.github.com/repos/dimagi/commcare/keys{/key_id}","collaborators_url":"https://api.github.com/repos/dimagi/commcare/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/dimagi/commcare/teams","hooks_url":"https://api.github.com/repos/dimagi/commcare/hooks","issue_events_url":"https://api.github.com/repos/dimagi/commcare/issues/events{/number}","events_url":"https://api.github.com/repos/dimagi/commcare/events","assignees_url":"https://api.github.com/repos/dimagi/commcare/assignees{/user}","branches_url":"https://api.github.com/repos/dimagi/commcare/branches{/branch}","tags_url":"https://api.github.com/repos/dimagi/commcare/tags","blobs_url":"https://api.github.com/repos/dimagi/commcare/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/dimagi/commcare/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/dimagi/commcare/git/refs{/sha}","trees_url":"https://api.github.com/repos/dimagi/commcare/git/trees{/sha}","statuses_url":"https://api.github.com/repos/dimagi/commcare/statuses/{sha}","languages_url":"https://api.github.com/repos/dimagi/commcare/languages","stargazers_url":"https://api.github.com/repos/dimagi/commcare/stargazers","contributors_url":"https://api.github.com/repos/dimagi/commcare/contributors","subscribers_url":"https://api.github.com/repos/dimagi/commcare/subscribers","subscription_url":"https://api.github.com/repos/dimagi/commcare/subscription","commits_url":"https://api.github.com/repos/dimagi/commcare/commits{/sha}","git_commits_url":"https://api.github.com/repos/dimagi/commcare/git/commits{/sha}","comments_url":"https://api.github.com/repos/dimagi/commcare/comments{/number}","issue_comment_url":"https://api.github.com/repos/dimagi/commcare/issues/comments{/number}","contents_url":"https://api.github.com/repos/dimagi/commcare/contents/{+path}","compare_url":"https://api.github.com/repos/dimagi/commcare/compare/{base}...{head}","merges_url":"https://api.github.com/repos/dimagi/commcare/merges","archive_url":"https://api.github.com/repos/dimagi/commcare/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/dimagi/commcare/downloads","issues_url":"https://api.github.com/repos/dimagi/commcare/issues{/number}","pulls_url":"https://api.github.com/repos/dimagi/commcare/pulls{/number}","milestones_url":"https://api.github.com/repos/dimagi/commcare/milestones{/number}","notifications_url":"https://api.github.com/repos/dimagi/commcare/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/dimagi/commcare/labels{/name}","releases_url":"https://api.github.com/repos/dimagi/commcare/releases{/id}","created_at":"2014-06-17T21:13:25Z","updated_at":"2015-05-01T15:55:40Z","pushed_at":"2015-10-19T16:21:16Z","git_url":"git://github.com/dimagi/commcare.git","ssh_url":"git@github.com:dimagi/commcare.git","clone_url":"https://github.com/dimagi/commcare.git","svn_url":"https://github.com/dimagi/commcare","homepage":null,"size":8416,"stargazers_count":2,"watchers_count":2,"language":"Java","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":4,"mirror_url":null,"open_issues_count":4,"forks":4,"open_issues":4,"watchers":2,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/101"},"html":{"href":"https://github.com/dimagi/commcare/pull/101"},"issue":{"href":"https://api.github.com/repos/dimagi/commcare/issues/101"},"comments":{"href":"https://api.github.com/repos/dimagi/commcare/issues/101/comments"},"review_comments":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/101/comments"},"review_comment":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/dimagi/commcare/pulls/101/commits"},"statuses":{"href":"https://api.github.com/repos/dimagi/commcare/statuses/385c2f0537514f6b0b7519bc573bdfce5574cfed"}}}}