Tokio-postgres and database query

[Mike-Kharkov]

There is such a module code (for working with a database):

use tokio_postgres::{NoTls, Error};

pub async fn hello() -> Result<(), Error> {

    //println!("hello() from db.rs");

    // Connect to the database.
    let (client, connection) =
        tokio_postgres::connect("host=localhost user=postgres", NoTls).await?;

    // The connection object performs the actual communication with the database,
    // so spawn it off to run on its own.
    tokio::spawn(async move {
        if let Err(e) = connection.await {
            eprintln!("connection error: {}", e);
        }
    });

    // Now we can execute a simple statement that just returns its parameter.
    let rows = client
        .query("SELECT $1::TEXT", &[&"hello world"])
        .await?;

    println!("{:?}", rows);

    // And then check that we got back the same string we sent over.
    let value: &str = rows[0].get(0);
    assert_eq!(value, "hello world");

    Ok(())
}

Question:

How, in this case, the access to the database should be written?

(the guide doesn't say anything about it - or I didn't fully understand it.)

https://docs.rs/tokio-postgres/0.5.5/tokio_postgres/

What mechanisms in this case will protect access to the database from sql injections?

The simplest general use case is needed.

[ZiCog]

@Mike-Kharkov

How, in this case, the access to the database should be written?

I'm not sure I understand your question. The code you posted there is an example of how to access the data base.

I had a very tough time getting postgres crate to work with my Rocket web server some months ago. Then I had a bit of a struggle figuring out how to upgrade to the postgres version "0.17.5" and getting that to work again.

Anyway below is an example of how I have it working now.

Typically SQL injections happen because strings input by a user are used directly as input data in building SQL query strings. Without parsing and 'sanitizing' those strings a user can input all kind of SQL syntax and cause havoc.

My example below takes HTTP query parameters as input of a date specification. But it is not used directly, first it is parsed by NaiveDate::parse_from_str().

// Some data model for Rocket to use a state
struct Model {
    // Whatever your model is...
}

impl Model {
    // Make database connection 
    fn connect(&self) -> postgres::Client {
        let mut builder = SslConnector::builder(SslMethod::tls()).unwrap();

        builder.set_ca_file("/somewhere/certs/ca.crt").unwrap();
        builder
            .set_certificate_chain_file("/somewhere/certs/client.root.crt")
            .unwrap();
        builder
            .set_private_key_file("/somewhere/certs/client.root.key", SslFiletype::PEM)
            .unwrap();

        let connector = MakeTlsConnector::new(builder.build());

        let mut client =
            postgres::Client::connect("postgres://you@your_db_server.com:26257", connector)
                .unwrap();

        client.execute("USE yourdb", &[]).unwrap();
        client
    }
}
...
...

#[derive(Serialize)]
struct Trip {
    t_stamp: DateTime<Utc>,
    trid: Option<i64>,
    trav_time: Option<f64>,
    trav_dist: Option<f64>,
}

#[derive(Serialize)]
struct Trips {
    trips: Vec<Trip>,
}

#[get("/public/api/trips/<date>", rank = 1)]
fn api_trips(model: State<Model>, date: String) -> Json<Trips> {
    let _current_count = model.count.fetch_add(1, Ordering::Relaxed);

    // Validate date string by parsing into date structure.
    let date_valid = NaiveDate::parse_from_str(&date, "%Y-%m-%d").unwrap();

    let mut conn = model.connect();
    let query = format!(
        "SELECT * FROM trips WHERE DATE_TRUNC('day', t_stamp) = '{}'",
        date_valid
    );
    let prepared = conn.prepare(&query).unwrap();

    let rows = conn.query(&prepared, &[]).unwrap();

    let mut results = Trips { trips: vec![] };

    for row in &rows {
        let trip = Trip {
            t_stamp: row.get(0),
            trid: row.get(1),
            trav_time: row.get(2),
            trav_dist: row.get(3),
        };

        results.trips.push(trip);
    }
    Json(results)
}

That is not the tokio-postgres as Rocket is not async yet but I guess it would look similar.

[ZiCog]

@Mike-Kharkov

The simplest general use case is needed.

You spurred me on to revisit the first ever experiment with a postgres connection from Rust that I made last year. I adapted it to use tokio-postgres. It makes some simple queries by way of example:

use openssl::ssl::{SslConnector, SslFiletype, SslMethod};
use postgres_openssl::MakeTlsConnector;

struct Person {
    id: i64,
    name: String,
    data: Option<Vec<u8>>,
}

pub async fn demo_secure_tokio() {
    // Create a TLS connector
    let mut builder = SslConnector::builder(SslMethod::tls()).unwrap();
    builder.set_ca_file("../certs/ca.crt").unwrap();
    builder
        .set_certificate_chain_file("../certs/client.root.crt")
        .unwrap();
    builder
        .set_private_key_file("../certs/client.root.key", SslFiletype::PEM)
        .unwrap();
    let connector = MakeTlsConnector::new(builder.build());

    // Connect to the database.
    let (client, connection) =
        tokio_postgres::connect("postgres://root@somewhere.com:26257", connector)
            .await
            .unwrap();

    // The connection object performs the actual communication with the database,
    // so spawn it off to run on its own.
    tokio::spawn(async move {
        if let Err(e) = connection.await {
            eprintln!("connection error: {}", e);
        }
    });

    println!("db connected.");

    client.execute("USE defaultdb", &[]).await.unwrap();

    client
        .execute(
            "CREATE TABLE IF NOT EXISTS person (
                    id              SERIAL PRIMARY KEY,
                    name            VARCHAR NOT NULL,
                    data            BYTEA
                  )",
            &[],
        )
        .await
        .unwrap();

    let me = Person {
        id: 1,
        name: "Snoopy".to_string(),
        data: None,
    };

    // NB: This does not insert the id.
    client
        .execute(
            "INSERT INTO person (name, data) VALUES ($1, $2)",
            &[&me.name, &me.data],
        )
        .await
        .unwrap();

    for row in &client
        .query("SELECT id, name, data FROM person", &[])
        .await
        .unwrap()
    {
        let person = Person {
            id: row.get(0),
            name: row.get(1),
            data: row.get(2),
        };
        println!("Found person {}: {}", person.id, person.name);
    }
}

I have these deps in Cargo.toml:

[dependencies]
openssl = "0.10.30"
postgres-openssl = "0.3.0"
tokio = { version = "0.2.21", features = ["full"] }
tokio-postgres = "0.5.5"