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

@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);
}
 

@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());
}
 

@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());
}
 

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();
    }
}
 

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;
   }
}
 

@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);
}
 

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();
    }
}
 

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();
    }
  }
}
 

@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());
    }
}
 

@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);
}
 

@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);
}
 

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, KeyValuePair pair) throws Exception {
   stmt.bind(1, pair.getKey());
   DbUtils.bind(stmt, pair.getValue(), 2);
}
 

@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);
}
 

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, KeyValue pair) throws Exception {
   stmt.bind(1, pair.key);
   DbUtils.bind(stmt, pair.value, 2);
}
 

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, String value) throws Exception {
   stmt.bind(1, value);
}
 

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();
    }
}
 

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, KeyValuePair pair) throws Exception {
   stmt.bind(1, pair.key);
   DbUtils.bind(stmt, pair.value, 2);
}
 

@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, String value) throws Exception {
   stmt.bind(1, value);
}
 

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;
}
 

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();
    }
}