libpq only inserts first character in string column

Question

So I have this structure in my psql table called rooms:

                                         Table "public.rooms"
         Column          |          Type          |                     Modifiers
-------------------------+------------------------+----------------------------------------------------
 id                      | integer                | not null default nextval('rooms_id_seq'::regclass)
 room_id                 | integer                |
 room_name               | character varying(100) |
 room_secret             | character varying(255) |
 room_pin                | character varying(100) |
 room_communication_type | character varying(30)  |
Indexes:
    "rooms_pkey" PRIMARY KEY, btree (id)
    "index_rooms_on_room_id" UNIQUE, btree (room_id)
    "index_rooms_on_room_name" btree (room_name)
    "index_rooms_on_room_secret" btree (room_secret)

And when I try to perform and insert statement from my C program, I get these results:

janus_audiobridge=# SELECT * FROM rooms;
 id | room_id | room_name | room_secret | room_pin | room_communication_type
----+---------+-----------+-------------+----------+-------------------------
  1 |  111128 | I         | L           |          | r
  3 |  111129 | C         | c           |          | r
  4 |  111130 | 4         | b           |          | r
  6 |  111131 | 5         | b           |          | r
(4 rows)

Notice the room_name, room_secret and room_communication_type only contain 1 character, the origin al data i wanted insert is full sentences of text.

This is my C code:

  const char *paramValues[5];
  paramValues[0] = "111131";
  paramValues[1] = room->room_name;
  paramValues[2] = room->room_secret;
  paramValues[3] = room->room_pin;
  paramValues[4] = room->room_communication_type;


  JANUS_LOG(LOG_WARN, "name: %s", paramValues[1]);

  JANUS_LOG(LOG_ERR, "44444\n\n\n");
  PGresult *res = PQexecParams(conn, query,
    5,       /* one param */
    NULL,    /* let the backend deduce param type */
    paramValues,
    NULL,    /* don't need param lengths since text */
    NULL,    /* default to all text params */
    0);      /* ask for text results */

When I run the INSERT the line JANUS_LOG returns correct name, but inserts only the first character.

How can i insert full text into postgres database using libpq?

Thanks!

EDIT: my query string:

gchar *query = "INSERT INTO rooms(room_id, room_name, room_secret, room_pin, room_communication_type) " 
"VALUES($1::int, $2::char, $3::char, $4::char, $5::char);";

And where is your `PQprepare ()` call ? And what is the definition for `struct room` ? — wildplasser, Mar 06 '17 at 15:23

Laurenz Albe · Accepted Answer · 2017-03-07T08:30:10.977

3

Remove the cast to char in your INSERT statement (that is the same as character(1)). Then everything should work fine. The cast to int is also unnecessary.

You can see what happens with this simple experiment in psql:

SELECT 'hello'::char;
 bpchar
--------
 h
(1 row)

Casting to character varying would have worked, but it is unnecessary to cast explicitly, because during INSERT an assignment cast will be performed anyway.

As the documentation states:

A cast applied to an unadorned string literal represents the initial assignment of a type to a literal constant value, and so it will succeed for any type (if the contents of the string literal are acceptable input syntax for the data type).

edited Mar 07 '17 at 08:30

answered Mar 06 '17 at 15:37

Laurenz Albe

209,280
17
206
263

It worked. By why?! I was following the guidelines. few questions: Is my code secure now(when i removed the cast)? – IvRRimUm Mar 06 '17 at 15:41
2

Which guidelines? – wildplasser Mar 06 '17 at 15:48
In the examples of `PQexecParams` :/ – IvRRimUm Mar 07 '17 at 08:30
1

@IvRRimUm I extended the answer to cover the "why". With that modification, your code looks ok (and safe from SQL injection). – Laurenz Albe Mar 07 '17 at 08:31
1

I guess you mean [this](https://www.postgresql.org/docs/current/static/libpq-example.html) sample program. In that example a binary parameter is used, and the comment says: *This is a good safety measure when sending **binary parameters**.* – Laurenz Albe Mar 07 '17 at 08:37

libpq only inserts first character in string column

1 Answers1