Java Code Examples for com.almworks.sqlite4java.SQLiteStatement#bind()

The following examples show how to use com.almworks.sqlite4java.SQLiteStatement#bind() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: AttestationServer.java    From AttestationServer with MIT License 6 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final Account account = verifySession(exchange, false, null);
    if (account == null) {
        return;
    }
    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        final SQLiteStatement select = conn.prepare("DELETE from Sessions where userId = ?");
        select.bind(1, account.userId);
        select.step();
        select.dispose();
    } finally {
        conn.dispose();
    }
    clearCookie(exchange);
    exchange.sendResponseHeaders(200, -1);
}
 
Example 2
Source File: Binders.java    From arcusplatform with Apache License 2.0 6 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, ZWNode node) throws Exception {
   int idx = 1;
   stmt.bind(idx++, node.getNodeId());
   stmt.bind(idx++, node.getBasicDeviceType());
   stmt.bind(idx++, node.getGenericDeviceType());
   stmt.bind(idx++, node.getSpecificDeviceType());
   stmt.bind(idx++, node.getManufacturerId());
   stmt.bind(idx++, node.getProductTypeId());
   stmt.bind(idx++, node.getProductId());
   stmt.bind(idx++, ByteUtils.byteArray2SpacedString(node.getCmdClassBytes()));
   stmt.bind(idx++, node.isOnline() ? 1 : 0);
   stmt.bind(idx++, node.getOfflineTimeout());
   
   // The where clause
   stmt.bind(idx++, node.getNodeId());
}
 
Example 3
Source File: Binders.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, ZWNode node) throws Exception {
   int idx = 1;
   stmt.bind(idx++, node.getNodeId());
   stmt.bind(idx++, node.getBasicDeviceType());
   stmt.bind(idx++, node.getGenericDeviceType());
   stmt.bind(idx++, node.getSpecificDeviceType());
   stmt.bind(idx++, node.getManufacturerId());
   stmt.bind(idx++, node.getProductTypeId());
   stmt.bind(idx++, node.getProductId());
   stmt.bind(idx++, ByteUtils.byteArray2SpacedString(node.getCmdClassBytes()));
   stmt.bind(idx++, node.isOnline() ? 1 : 0);
   stmt.bind(idx++, node.getOfflineTimeout());
}
 
Example 4
Source File: AttestationServer.java    From AttestationServer with MIT License 5 votes vote down vote up
private static void createAccount(final String username, final String password)
        throws GeneralSecurityException, SQLiteException {
    if (username.length() > 32 || !username.matches("[a-zA-Z0-9]+")) {
        throw new GeneralSecurityException("invalid username");
    }
    validatePassword(password);

    final byte[] passwordSalt = generateRandomToken();
    final byte[] passwordHash = hash(password.getBytes(), passwordSalt);
    final byte[] subscribeKey = generateRandomToken();

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);
        final SQLiteStatement insert = conn.prepare("INSERT INTO Accounts " +
                "(username, passwordHash, passwordSalt, subscribeKey, creationTime, verifyInterval, alertDelay) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)");
        insert.bind(1, username);
        insert.bind(2, passwordHash);
        insert.bind(3, passwordSalt);
        insert.bind(4, subscribeKey);
        insert.bind(5, System.currentTimeMillis());
        insert.bind(6, DEFAULT_VERIFY_INTERVAL);
        insert.bind(7, DEFAULT_ALERT_DELAY);
        insert.step();
        insert.dispose();
    } catch (final SQLiteException e) {
        if (e.getErrorCode() == SQLITE_CONSTRAINT_UNIQUE) {
            throw new UsernameUnavailableException();
        }
        throw e;
    } finally {
        conn.dispose();
    }
}
 
Example 5
Source File: DbUtils.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
private static boolean doBind(@Nullable Class<?> type, SQLiteStatement stmt, @Nullable Object arg, int idx) throws Exception {
   if (arg == null) {
      stmt.bindNull(idx);
      return true;
   } else if (type == String.class) {
      stmt.bind(idx, (String) arg);
      return true;
   } else if ( type == boolean.class || type == Boolean.class) {
      Integer i = ((boolean) arg) ? 1 : 0;
      stmt.bind(idx, i);
      return true;
   } else if (type == byte.class || type == Byte.class) {
      stmt.bind(idx, (byte) arg);
      return true;
   } else if (type == short.class || type == Short.class) {
      stmt.bind(idx, (short) arg);
      return true;
   } else if (type == int.class || type == Integer.class) {
      stmt.bind(idx, (int) arg);
      return true;
   } else if (type == long.class || type == Long.class) {
      stmt.bind(idx, (long) arg);
      return true;
   } else if (type == float.class || type == Float.class) {
      stmt.bind(idx, (float) arg);
      return true;
   } else if (type == double.class || type == Double.class) {
      stmt.bind(idx, (double) arg);
      return true;
   } else if (type == byte[].class) {
      stmt.bind(idx, (byte[]) arg);
      return true;
   } else {
      return false;
   }
}
 
Example 6
Source File: AttestationServer.java    From AttestationServer with MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final InputStream input = exchange.getRequestBody();

    final ByteArrayOutputStream sample = new ByteArrayOutputStream();
    final byte[] buffer = new byte[4096];
    for (int read = input.read(buffer); read != -1; read = input.read(buffer)) {
        sample.write(buffer, 0, read);

        if (sample.size() > 64 * 1024) {
            exchange.sendResponseHeaders(413, -1);
            return;
        }
    }

    final SQLiteConnection conn = new SQLiteConnection(SAMPLES_DATABASE);
    try {
        open(conn, false);
        final SQLiteStatement insert = conn.prepare("INSERT INTO Samples " +
               "(sample, time) VALUES (?, ?)");
        insert.bind(1, sample.toByteArray());
        insert.bind(2, System.currentTimeMillis());
        insert.step();
        insert.dispose();
    } finally {
        conn.dispose();
    }

    exchange.sendResponseHeaders(200, -1);
}
 
Example 7
Source File: AttestationServer.java    From AttestationServer with MIT License 5 votes vote down vote up
private static void changePassword(final long userId, final String currentPassword, final String newPassword)
        throws GeneralSecurityException, SQLiteException {
    validatePassword(currentPassword);
    validatePassword(newPassword);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        conn.exec("BEGIN TRANSACTION");

        final SQLiteStatement select = conn.prepare("SELECT passwordHash, passwordSalt " +
                "FROM Accounts WHERE userId = ?");
        select.bind(1, userId);
        select.step();
        final byte[] currentPasswordHash = select.columnBlob(0);
        final byte[] currentPasswordSalt = select.columnBlob(1);
        select.dispose();
        if (!MessageDigest.isEqual(hash(currentPassword.getBytes(), currentPasswordSalt), currentPasswordHash)) {
            throw new GeneralSecurityException("invalid password");
        }

        final byte[] newPasswordSalt = generateRandomToken();
        final byte[] newPasswordHash = hash(newPassword.getBytes(), newPasswordSalt);

        final SQLiteStatement update = conn.prepare("UPDATE Accounts " +
                "SET passwordHash = ?, passwordSalt = ? WHERE userId = ?");
        update.bind(1, newPasswordHash);
        update.bind(2, newPasswordSalt);
        update.bind(3, userId);
        update.step();
        update.dispose();

        conn.exec("COMMIT TRANSACTION");
    } finally {
        conn.dispose();
    }
}
 
Example 8
Source File: MbVectorTilesInputFormat.java    From mrgeo with Apache License 2.0 5 votes vote down vote up
protected long getRecordCount(Configuration conf) throws IOException
{
  String countQuery = "SELECT COUNT(*) FROM tiles WHERE zoom_level=?";
  // Run the count query and grab the result.
  SQLiteConnection conn = null;
  try {
    conn = MbVectorTilesDataProvider.getDbConnection(dbSettings, conf);
    SQLiteStatement stmt = null;
    try {
      stmt = conn.prepare(countQuery, false);
      stmt.bind(1, zoomLevel);
      if (stmt.step()) {
        return stmt.columnLong(0);
      }
      else {
        throw new IOException("Unable to count tiles for zoom " + zoomLevel + " in " + dbSettings.getFilename());
      }
    }
    finally {
      if (stmt != null) {
        stmt.dispose();
      }
    }
  }
  catch (SQLiteException e)
  {
    String msg = "Unable to get the count of records using query: " + countQuery;
    log.error(msg, e);
    throw new IOException(msg, e);
  }
  finally {
    if (conn != null) {
      conn.dispose();
    }
  }
}
 
Example 9
Source File: AttestationServer.java    From AttestationServer with MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final Account account = verifySession(exchange, false, null);
    if (account == null) {
        return;
    }
    final JsonObjectBuilder accountJson = Json.createObjectBuilder();
    accountJson.add("username", account.username);
    accountJson.add("verifyInterval", account.verifyInterval);
    accountJson.add("alertDelay", account.alertDelay);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, true);
        final SQLiteStatement select = conn.prepare("SELECT address FROM EmailAddresses " +
                "WHERE userId = ?");
        select.bind(1, account.userId);
        if (select.step()) {
            accountJson.add("email", select.columnString(0));
        }
        select.dispose();
    } finally {
        conn.dispose();
    }

    exchange.getResponseHeaders().set("Content-Type", "application/json");
    exchange.sendResponseHeaders(200, 0);
    try (final OutputStream output = exchange.getResponseBody();
            final JsonWriter writer = Json.createWriter(output)) {
        writer.write(accountJson.build());
    }
}
 
Example 10
Source File: AttestationServer.java    From AttestationServer with MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final String requestToken;
    final String fingerprint;
    try (final JsonReader reader = Json.createReader(exchange.getRequestBody())) {
        final JsonObject object = reader.readObject();
        requestToken = object.getString("requestToken");
        fingerprint = object.getString("fingerprint");
    } catch (final ClassCastException | JsonException | NullPointerException e) {
        e.printStackTrace();
        exchange.sendResponseHeaders(400, -1);
        return;
    }

    final Account account = verifySession(exchange, false, requestToken.getBytes(StandardCharsets.UTF_8));
    if (account == null) {
        return;
    }

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        final SQLiteStatement update = conn.prepare("UPDATE Devices SET " +
                "deletionTime = ? WHERE userId = ? AND hex(fingerprint) = ?");
        update.bind(1, System.currentTimeMillis());
        update.bind(2, account.userId);
        update.bind(3, fingerprint);
        update.step();
        update.dispose();

        if (conn.getChanges() == 0) {
            exchange.sendResponseHeaders(400, -1);
            return;
        }
    } finally {
        conn.dispose();
    }
    exchange.sendResponseHeaders(200, -1);
}
 
Example 11
Source File: TestDbService.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, TestPojo value) throws Exception {
   stmt.bind(1, value.key);
   stmt.bind(2, value.value1);
   stmt.bind(3, value.value2);
   stmt.bind(4, value.value3);
}
 
Example 12
Source File: Binders.java    From arcusplatform with Apache License 2.0 4 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, KeyValuePair pair) throws Exception {
   stmt.bind(1, pair.getKey());
   DbUtils.bind(stmt, pair.getValue(), 2);
}
 
Example 13
Source File: ReflexDao.java    From arcusplatform with Apache License 2.0 4 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, AddressKeyValue akv) throws Exception {
   stmt.bind(1, akv.addr);
   stmt.bind(2, akv.key);
   stmt.bind(3, akv.value);
}
 
Example 14
Source File: ReflexDao.java    From arcusplatform with Apache License 2.0 4 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, KeyValue pair) throws Exception {
   stmt.bind(1, pair.key);
   DbUtils.bind(stmt, pair.value, 2);
}
 
Example 15
Source File: ReflexDao.java    From arcusplatform with Apache License 2.0 4 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, String value) throws Exception {
   stmt.bind(1, value);
}
 
Example 16
Source File: AttestationServer.java    From AttestationServer with MIT License 4 votes vote down vote up
private static Account verifySession(final HttpExchange exchange, final boolean end, byte[] requestTokenEncoded)
        throws IOException, SQLiteException {
    final String cookie = getCookie(exchange, "__Host-session");
    if (cookie == null) {
        exchange.sendResponseHeaders(403, -1);
        return null;
    }
    final String[] session = cookie.split("\\|", 2);
    if (session.length != 2) {
        clearCookie(exchange);
        exchange.sendResponseHeaders(403, -1);
        return null;
    }
    final long sessionId = Long.parseLong(session[0]);
    final byte[] cookieToken = Base64.getDecoder().decode(session[1]);

    if (requestTokenEncoded == null) {
        requestTokenEncoded = new byte[session[1].length()];
        final DataInputStream input = new DataInputStream(exchange.getRequestBody());
        try {
            input.readFully(requestTokenEncoded);
        } catch (final EOFException e) {
            clearCookie(exchange);
            exchange.sendResponseHeaders(403, -1);
            return null;
        }
    }
    final byte[] requestToken = Base64.getDecoder().decode(requestTokenEncoded);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, !end);

        final SQLiteStatement select = conn.prepare("SELECT cookieToken, requestToken, " +
                "expiryTime, username, subscribeKey, Accounts.userId, verifyInterval, alertDelay " +
                "FROM Sessions " +
                "INNER JOIN Accounts on Accounts.userId = Sessions.userId " +
                "WHERE sessionId = ?");
        select.bind(1, sessionId);
        if (!select.step() || !MessageDigest.isEqual(cookieToken, select.columnBlob(0)) ||
                !MessageDigest.isEqual(requestToken, select.columnBlob(1))) {
            clearCookie(exchange);
            exchange.sendResponseHeaders(403, -1);
            return null;
        }

        if (select.columnLong(2) < System.currentTimeMillis()) {
            clearCookie(exchange);
            exchange.sendResponseHeaders(403, -1);
            return null;
        }

        if (end) {
            final SQLiteStatement delete = conn.prepare("DELETE FROM Sessions " +
                    "WHERE sessionId = ?");
            delete.bind(1, sessionId);
            delete.step();
            delete.dispose();
        }

        return new Account(select.columnLong(5), select.columnString(3), select.columnBlob(4),
                select.columnInt(6), select.columnInt(7));
    } finally {
        conn.dispose();
    }
}
 
Example 17
Source File: BackupUtils.java    From arcusplatform with Apache License 2.0 4 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, KeyValuePair pair) throws Exception {
   stmt.bind(1, pair.key);
   DbUtils.bind(stmt, pair.value, 2);
}
 
Example 18
Source File: ConfigService.java    From arcusplatform with Apache License 2.0 4 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, String value) throws Exception {
   stmt.bind(1, value);
}
 
Example 19
Source File: SQLiteWrapperUtils.java    From sync-android with Apache License 2.0 4 votes vote down vote up
public static SQLiteStatement bindArguments(SQLiteStatement stmt, Object[] bindArgs)
        throws SQLiteException {

    if(bindArgs == null) {
        bindArgs = new Object[]{};
    }

    final int count = bindArgs.length;
    if (count != stmt.getBindParameterCount()) {
        throw new IllegalArgumentException(
                "Expected " + stmt.getBindParameterCount() + " bind arguments but "
                        + bindArgs.length + " were provided.");
    }
    if (count == 0) {
        return stmt;
    }

    for (int i = 0; i < count; i++) {
        final Object arg = bindArgs[i];
        switch (DBUtils.getTypeOfObject(arg)) {
            case Cursor.FIELD_TYPE_NULL:
                stmt.bindNull(i + 1);
                break;
            case Cursor.FIELD_TYPE_INTEGER:
                stmt.bind(i + 1, ((Number) arg).longValue());
                break;
            case Cursor.FIELD_TYPE_FLOAT:
                stmt.bind(i + 1, ((Number) arg).doubleValue());
                break;
            case Cursor.FIELD_TYPE_BLOB:
                stmt.bind(i + 1, (byte[]) arg);
                break;
            case Cursor.FIELD_TYPE_STRING:
            default:
                if (arg instanceof Boolean) {
                    // Provide compatibility with legacy applications which may pass
                    // Boolean values in bind args.
                    stmt.bind(i + 1, ((Boolean) arg).booleanValue() ? 1 : 0);
                } else {
                    stmt.bind(i + 1, arg.toString());
                }
                break;
        }
    }

    return stmt;
}
 
Example 20
Source File: AttestationServer.java    From AttestationServer with MIT License 4 votes vote down vote up
private static Session login(final String username, final String password)
        throws GeneralSecurityException, SQLiteException {
    validatePassword(password);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);
        final SQLiteStatement select = conn.prepare("SELECT userId, passwordHash, " +
                "passwordSalt FROM Accounts WHERE username = ?");
        select.bind(1, username);
        if (!select.step()) {
            throw new UsernameUnavailableException();
        }
        final long userId = select.columnLong(0);
        final byte[] passwordHash = select.columnBlob(1);
        final byte[] passwordSalt = select.columnBlob(2);
        select.dispose();
        if (!MessageDigest.isEqual(hash(password.getBytes(), passwordSalt), passwordHash)) {
            throw new GeneralSecurityException("invalid password");
        }

        final long now = System.currentTimeMillis();
        final SQLiteStatement delete = conn.prepare("DELETE FROM Sessions WHERE expiryTime < ?");
        delete.bind(1, now);
        delete.step();
        delete.dispose();

        final byte[] cookieToken = generateRandomToken();
        final byte[] requestToken = generateRandomToken();

        final SQLiteStatement insert = conn.prepare("INSERT INTO Sessions " +
                "(userId, cookieToken, requestToken, expiryTime) VALUES (?, ?, ?, ?)");
        insert.bind(1, userId);
        insert.bind(2, cookieToken);
        insert.bind(3, requestToken);
        insert.bind(4, now + SESSION_LENGTH);
        insert.step();
        insert.dispose();

        return new Session(conn.getLastInsertId(), cookieToken, requestToken);
    } finally {
        conn.dispose();
    }
}