Is this the best way to replace characters in a string?


A snag I hit in my blog tutorial was text needed to be checked for single quotes before inserting them into an SQL database, and if so the single quotes needed to be escaped SQL-style.

My solution is:

sql_escape_single_quotes(StringIn, StringOut) :-
  split_string(StringIn, "'", "", List),
  atomics_to_string(List, "''", StringOut).

It seems to work well, and I’m very proud of it… which probably means there’s some horrible bug lurking in there somewhere.

BTW: I’ve nearly finished Unit 3 which explains how to join a PostgreSQL database to a SWI Prolog web application at


My 2 cents worth.

Did you write test cases for it and check the boundary conditions? If so and it works then move on. To quote Donald Knuth.

" premature optimization is the root of all evil (or at least most of it) in programming .

Remember you can always update it later. Most people who read your blog are probably more interested in seeing the dots connected than seeing perfect shinny dots. :slightly_smiling_face:

1 Like

Is this needed? The ODBC interface allows using "?"s in the query, which are substituted at query execution time.


I’m a big fan of literate programming and test driven development, and hope to write a follow up howto on those for SWI Prolog at some stage. (I’m not aware of good ways of doing these for web development, other than eyeballing the error messages in the browser if something is broken).

A reason I’m working through the old Udacity course again and re-implementing it in SWI Prolog is it avoids the common tutorial problem of sticking to examples a given language is good at and there are often repeated solutions for, and there was no way of avoiding making ASCII art not make odbc_query choke without solving the escaping single quote problem in a way which I assume is a common problem in Prolog.

I only realised Steve Huffman’s genius in picking that project while I was working through it.