2

I have this code snippet in a view:

time_from = datetime.strptime(req.POST['time_from'], "%Y-%m-%d %H:%M:%S.%f")

with connections["mssql_database"].cursor() as cursor:
                sql_statement = "EXEC SaveActivity @TimeFrom='%s'" % (time_from)
                print(sql_statement)
                cursor.execute(sql_statement)

It prints this SQL statement:

EXEC SaveActivity @TimeFrom='2021-12-01 08:34:54'

Microseconds are missing. They are zeros, but I need them in a database. How can I correct this?

xralf
  • 3,312
  • 45
  • 129
  • 200

1 Answers1

2

Don't perform string formatting. Pass the data as parameter. By using string formatting, it will use the str(…), which is for a datetime not very precise. Use:

with connections["mssql_database"].cursor() as cursor:
    sql_statement = "EXEC SaveActivity @TimeFrom='%s'"
    cursor.execute(sql_statement, (time_from,))

Using parameters over string interpolation is not only better to prevent data loss, it also prevents SQL injection where one might pass, for example through a string, an SQL query that will then modify or leak the database.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • Thank you. I haven't tested yet. I have rewritten it according you, but now I have error `ql = sql % tuple('?' * len(params)) TypeError: %d format: a number is required, not str` Actually I have more than one parameters. Can I print the sql_statement for debugging purposes? – xralf Dec 21 '21 at 11:37
  • @xralf: if you have more than one parameter, you need to add these all to the second 2-tuple, and use `%s`, not `%d`. – Willem Van Onsem Dec 21 '21 at 11:38
  • I know, but I have so many parameters, it's difficult to see, so I would like to print the sql statement, if possible. – xralf Dec 21 '21 at 11:39
  • 1
    @xralf: you can rpint these with `connections['mssql_database'].queries`, for example the last one with `connections['mssql_database'].queries[-1]` The point is however that the parameters are *not* interpolated in the query itself. This to *protect* the query from SQL injection. – Willem Van Onsem Dec 21 '21 at 11:40
  • I tried your query, but the datetime was not converted at all. I used `try..except` and after it `connections['mssql_database'].queries[-1]` and it has written `{'sql': "QUERY = 'EXEC [dbVyroba].[dbo].[UlozitCinnost] @idpracoviste=%s, @OsobniCislo=%s, @IDcinnost=%s, @idklient=%s ,@pocet=%s, @program=%s, @CasOd=%s, @CasDO=%s, @IdOd=%s' - PARAMS = (1, '0711', 6, 3, 200, 0, datetime.datetime(2021, 12, 1, 8, 34, 54), datetime.datetime(2021, 12, 2, 22, 28), 0)", 'time': '0.045'}` – xralf Dec 21 '21 at 21:32
  • @xraf: no indeed, because that should be unsafe. It does not perform string interpolation, since that would make it sensitive to SQL injection. – Willem Van Onsem Dec 21 '21 at 21:34
  • sorry, my mistake. I've got it. In the stored procedure there was a condition (I have not written it), which explaines everything to me. I was looking errors somewhere else. :-) – xralf Dec 21 '21 at 21:53