Python/MySQL How to insert a String using a variable, keeps changing "TRUE" to 1

Question

I have a table called led_status and a Field called "test_led":

mysql> describe led_status;
+------------------------------+------------+------+-----+---------+-------+
| Field                        | Type       | Null | Key | Default | Extra |
+------------------------------+------------+------+-----+---------+-------+
| test_led                     | varchar(5) | NO   |     | NULL    |       |
+------------------------------+------------+------+-----+---------+-------+

I am attempting to enter a string of either "TRUE" or "FALSE" (not int 1 or 0) with the following code:

def write_database_value(table,field,value):
    connect = mysql.connector.connect(user=db_info.username,
                              password=db_info.password,
                              host=db_info.servername,
                              database=db_info.database)
    cursor = connect.cursor()
    cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))
    connect.commit()
    cursor.close()
    connect.close()

def read_database_value(table,field):
    connect = mysql.connector.connect(user=db_info.username,
                              password=db_info.password,
                              host=db_info.servername,
                              database=db_info.database)
    cursor = connect.cursor(buffered=True)
    cursor.execute(("SELECT %s FROM %s") % (field, table))

    for data in cursor:
        database_value = data
        cursor.close()
    connect.close()
    return database_value

Which is called from this script:

def get_led_status(led):
    led_status = read_database_value("led_status", led)
    led_status = (led_status[0])
    return led_status

def is_pool_pump_running():
    pool_running = get_led_status("test_led")
    if pool_running == "TRUE":
        print("Pool Pump is Running, shutting it off now")
        write_database_value("led_status","test_led","FALSE")
    else:
        print("Pool Pump is NOT Running, turning it on now")
        write_database_value("led_status","test_led","TRUE")

However everytime I run the script it changes my "TRUE" to a 1 and my "FALSE" to a 0.

I am switching over from a flat file to a database and all of my code (4000 lines of it so far) uses "TRUE" and "FALSE" so I really don't want to have to rewrite it just to use 1 and 0 as opposed to "TRUE" and "FALSE".

Any thoughts would be greatly appreciated.

Two important things: (1) you don't use the `%` string formatting on SQL, it leads to SQL injection vulnerabilities (2) divide data and its representation: `0`/`1` or in Python `False`/`True` are the data used for storing and processing in computers, the strings `"TRUE"` and `"FALSE"` are how you present it to humans. — Klaus D., Jan 02 '19 at 05:25
Thanks @KlausD. It was my understanding that %s **is** the correct method and that cursor.execute specifically protects against SQL injection issues. [Posts](https://stackoverflow.com/questions/1307378/python-mysql-update-statement) on Stack Exchange pointed me in this direction. Is this information incorrect? Second, as I stated above, I am moving from a flat file that uses true/false representation and I really did not want to rewrite all the code to 0/1 when I moved to a database design, hence the reason I want to stick with true/false as a varchar instead of 0/1 as a bit or tinyint. — Richard, Jan 02 '19 at 16:38
The important difference is the `%` between the string and the arguments. It has to be a comma to be safe. — Klaus D., Jan 02 '19 at 17:46
@KlausD. AH!!! I was thinking of the wrong %! I will read up more on that and change it. — Richard, Jan 02 '19 at 17:50

frsechet · Answer 1 · 2019-01-02T07:07:51.390

1

MySQL does not have boolean types (true/false), they are automatically interpreted (and stored) as 0/1 (in your schema above, you even clearly state that you want tinyints, not regular 32-bit ints). Nothing you can do about that!

If you want to store the literal value "TRUE" / "FALSE" you should use varchar(5) type of column.

Side note: default value for those columns should be either 0 or 1, OR it should be nullable - you have set their default to NULL and the column not nullable, which can lead to errors down the way.

edited Jan 02 '19 at 07:07

answered Jan 02 '19 at 06:53

frsechet

770
5
14

I am using a varchar(5)! If you look at my table above, test_led is varchar(5) type. – Richard Jan 02 '19 at 16:18
@Richard you were not at the time I wrote the answer to this. It shows in the revisions of your post... – frsechet Jan 02 '19 at 17:02
actually the test_led was ALWAYS a varchar(5). It still does not work. – Richard Jan 02 '19 at 17:04
`def get_led_status(led): led_status = read_database_value("led_status", led) led_status = (led_status[0]) return led_status` – Richard Jan 02 '19 at 17:08
sorry, not sure how to format code in a comment :-) – Richard Jan 02 '19 at 17:11
@Richard update the question with all relevant data, other people may want to see it. What's in read database value then? Etc. Try to simplify your code and show everything that is relevant (but not a single line more). I have no idea what's in pool_running, it could be anything. – frsechet Jan 02 '19 at 17:31
Will Do, I was trying to keep it minimal as possible. pool_running is shown in the initial post. I'll update the question! – Richard Jan 02 '19 at 17:43

score 0 · Answer 2 · answered Jan 02 '19 at 07:00

0

As @frsechet answer pointed out, in SQL you only have 0/1 representation of boolean. If you want TRUE/FALSE strings then you need to change the column type to string/char(5).

Other option is, if you are using a common function to fetch/update the column value then you can define value mapping inside that function which converts TRUE/FALSE to 1/0 and vice versa.

answered Jan 02 '19 at 07:00

Arpit

12,767
3
27
40

I am using a varchar(5)! If you look at my table above, test_led is varchar(5) type. – Richard Jan 02 '19 at 16:24

score 0 · Accepted Answer · answered Jan 03 '19 at 03:51

Thank you to everyone that provided input. After a LOT of digging around I figured out my problem. Apparently, you can't parameterize a table name in a prepared statement which is exactly what I was attempting to do above. POST where I found the answer @Conspicuous Compiler was dead on with that statement.

The problem was in my write_database_value() function. This is the correct function that works as needed:

def write_database_value(table,column,value):
    try:
        connection = mysql.connector.connect(user=db_info.username,
                                      password=db_info.password,
                                      host=db_info.servername,
                                      database=db_info.database)
        cursor = connection.cursor(buffered=True)
        sql_update = "UPDATE " + table + " SET " + column + " = %s"
        cursor.execute(sql_update, (value,))
        connection.commit()
        cursor.close()
        connection.close()
    except Error as error :
        print("Failed to Update record in database: {}".format(error))
    finally:
        if(connection.is_connected()):
            connection.close

The big change:

From: cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))

to this:

sql_update = "UPDATE " + table + " SET " + column + " = %s"
cursor.execute(sql_update, (value,))

I was 100% going to say that this morning when reading your code, props for figuring it out yourself. As mentioned in the link in my answer, TRUE in whatever letter case is converted to 1. Your query was "UPDATE led_status SET TRUE", which is read as 1, not as the string "TRUE", because it is not wrapped in quotes. A "select test_led from led_status" would have helped you debug more quickly. Side note: it is recommended to use prepared statements rather than inserting strings into a query, for security reasons but also for the reason you experienced right here. — frsechet, Jan 03 '19 at 08:43
Example: what if instead of "true" someone wrote something along the lines of "1; drop database;"... — frsechet, Jan 03 '19 at 08:46

Python/MySQL How to insert a String using a variable, keeps changing "TRUE" to 1

3 Answers3